#coding:utf-8
from django.shortcuts import render,HttpResponse,redirect

# Create your views here.

from django.conf import settings
import MySQLdb
import json
from aliexpress.api import datelist
import datetime,time
import random
import urllib
import requests

import hashlib

from API import *

from aliexpress.models import Country,TranslateContent,ProductDeclare,StoreInfo,\
    OrderMemo,OrderDealMark,DeliveryInformation,ChooseLogistics,OrderDeal
from aliexpress.views import countryTanslate

from django.views.decorators.csrf import csrf_exempt

from django.conf import settings

import sys
reload(sys)
sys.setdefaultencoding( "utf-8" )


host = settings.DATABASES['erp']['HOST']
user = settings.DATABASES['erp']['USER']
passwd = settings.DATABASES['erp']['PASSWORD']
db = settings.DATABASES['erp']['NAME']
port = int(settings.DATABASES['erp']['PORT'])


def linkDB():
    conn=MySQLdb.connect(host=host,user=user,passwd=passwd,db=db,port=port,charset='utf8')
    #conn=MySQLdb.connect(host='120.76.134.53',user='remote_user',passwd='tradetrends',db='erp',port=3306,charset='utf8')
    return conn


def custom_sql(sql):
    from django.db import connection, transaction
    cursor = connection.cursor()

    # 数据修改操作——提交要求
    #cursor.execute(sql)
    #transaction.commit_unless_managed()

    # 数据检索操作,不需要提交
    cursor.execute(sql)
    rawData = cursor.fetchall()
    col_names = [desc[0] for desc in cursor.description]

    result = []
    for row in rawData:
        objDict = {}
        # 把每一行的数据遍历出来放到Dict中
        for index, value in enumerate(row):
            objDict[col_names[index]] = str(value)

        result.append(objDict)

    return result


def my_update_sql(sql):
    conn = linkDB()
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()

def my_custom_sql(sql):
    #conn=MySQLdb.connect(host='120.76.134.53',user='remote_user',passwd='tradetrends',db='t_crm',port=3306,charset='utf8')
    conn = linkDB()
    cursor = conn.cursor()

    # 数据修改操作——提交要求
    #cursor.execute(sql)
    #transaction.commit_unless_managed()

    # 数据检索操作,不需要提交
    cursor.execute(sql)
    rawData = cursor.fetchall()
    col_names = [desc[0] for desc in cursor.description]

    result = []
    for row in rawData:
        objDict = {}
        # 把每一行的数据遍历出来放到Dict中
        for index, value in enumerate(row):
            objDict[col_names[index]] = str(value)

        result.append(objDict)

    return result


def cpanel(request):
    return render(request, 'aside/erp/index.html', locals())


def demo(request):
    return render(request, 'aside/home.html', locals())


def buy(request):
    return render(request, 'aside/buy.html', locals())


def message(request):
    return render(request, 'aside/message-index.html', locals())


def smtLogisticsChannelSettings(request):
    sql = 'SELECT * FROM e_express_channel  ORDER BY enabled DESC'
    channelData = my_custom_sql(sql)
    return render(request, 'aside/erp/logistics-settings-smt.html', locals())

def yanwenLogisticsChannelSettings(request):
    sql = 'SELECT * FROM y_channel  ORDER BY enabled DESC'
    channelData = my_custom_sql(sql)
    return render(request, 'aside/erp/logistics-settings-yanwen.html', locals())


def enbleChannel(request):
    data = dict()
    data["error"] = True
    if not request.user.is_authenticated():
        data["msg"] = u'非法请求'.decode()

    serviceName = request.GET.get('code',None)
    checked = request.GET.get('checked',None)
    t = request.GET.get('type',None)
    if id and checked:
        if checked in ['0','1']:
            if t == 'child':
                sql = 'UPDATE e_smt_logistics_channel SET enabled=%s WHERE logisticsServiceId="%s"'%(checked,serviceName)
            elif t == 'yanwen':
                sql = 'UPDATE y_channel SET enabled=%s WHERE YWCHANNEL_ID=%s'%(checked,serviceName)
            else:
                sql = 'UPDATE e_express_channel SET enabled=%s WHERE serviceName="%s"'%(checked,serviceName)
            my_update_sql(sql)
            data["error"] = False
        else:
            data["msg"] = u'非法请求'.decode()

    else:
        data["msg"] = u'非法请求'.decode()
    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')

def TransferDate(str):

    #return datetime.datetime.strptime(str[:14], "%Y%m%d%H%M%S")
    return datetime.datetime.strptime(str, "%Y-%m-%d %H:%M:%S")


def TransferGroup(grouId):
    gps = ['Doll','Lego toys','Halloween','Educational toys','card toys','Soft toy']

    return gps[random.randrange(0,len(gps)-1)]


def TransferTemplate(templateId):
    temp = ['epack Chinapost Part of the free (发货地区: CN)','hangkongjingji 5yixia (发货地区: CN)','su die bei cup (发货地区: CN)	','xianshang fahuo 7 yixia (发货地区: CN)','dujiaoshouzhuanyong (发货地区: CN)']
    return temp[random.randrange(0,len(temp)-1)]


def getProductImages(productId):
    sql = 'SELECT image_url from e_product_img WHERE productId=%s'%productId
    data = my_custom_sql(sql)
    return data



def workDevBasic(request):
    skuCode = random_digital(4)+'-'+random_str(4)
    return render(request, 'aside/erp/work-devBasic.html', locals())

def freightCalculate(request):
    if not request.user.is_authenticated():
        return redirect('/ucenter/login/'+"?from="+request.get_full_path())

    allCountry = Country.objects.values('country_name_cn')
    return render(request, 'aside/erp/freight-calculate.html', locals())


def smtProductAdd(request):
    return render(request, 'aside/erp/smt-goods-add.html', locals())


def smtProduct(request):
    if not request.user.is_authenticated():
        return redirect('/ucenter/login/'+"?from="+request.get_full_path())
    user = request.user

    #获取用户授权查看的店铺
    sql = 'SELECT `store_id` from `sys_store_user` WHERE `user_id`="%d"'%(user.id)
    userStores = my_custom_sql(sql)

    page = request.GET.get('page','1')
    status = request.GET.get('status',None)


    searchTxt = request.GET.get('searchTxt',None)
    searchType = request.GET.get('searchType',None)
    goodsStock = request.GET.get('goodsStock',None)


    if len(userStores)>0:
        sids = ''
        for s in userStores:
            sids +=s["store_id"]
            sids +=','
        sql = 'SELECT productId,subject,groupId,bulkDiscount,bulkOrder,freightTemplateId,wsOfflineDate,imageURLs,productMaxPrice,productMinPrice,STORE_ID FROM `e_product` '
        sql += ' WHERE STORE_ID in (%s) '%sids.strip(',')


        if searchTxt:
            word = searchTxt.strip()
            word = word.replace('"','\\"')

            if searchType == '2':
                sql += 'AND `productId` like "%'+word+'%" '
            else:
                sql += 'AND `subject` like "%'+word+'%" '


        if status:
            if status == 'onSelling':
                sql += 'AND `productStatusType` ="onSelling" '
            elif status == 'offline':
                sql += 'AND `productStatusType` ="offline" '


        #print(sql)
        temp = my_custom_sql(sql)


        productData = []

        for item in temp:
            if item["groupId"] != 'None':
                group = TransferGroup(item["groupId"])
            else:
                group = u'未分配'

            dd = TransferDate(str(item["wsOfflineDate"]))
            wsOfflineDate = str(dd)
            x = (dd - datetime.datetime.now()).days
            if x <1:
                if x == 0:
                    y = (dd-datetime.datetime.now()).seconds
                    if y>=(60*60):
                        leftTime = u'%d小时'%(y/(60*60))
                    elif y> 60 and y<(60*60):
                        leftTime = u'%d分'%(y/(60))
                    else:
                        leftTime = u'%d秒'%y
                else:
                    leftTime = u'已经过期'

            else:
                leftTime = u'%d天'%x

            template = TransferTemplate(item["freightTemplateId"])

            #img = getProductImages(item["productId"])
            img = item["imageURLs"].split(';')

            if img:
                #thumbnail = img[0]["image_url"]
                thumbnail = img[0]

            else:
                thumbnail = 'None'


            #获取总库存
            sql = 'SELECT sum(ipmSkuStock) as stock FROM e_product_sku WHERE productId="%s"'%(item["productId"])
            stockData = my_custom_sql(sql)
            stock = stockData[0]["stock"].split('.')[0]


            if item["productMaxPrice"] != item["productMinPrice"]:
                productPrice = '%s-%s'%(item["productMinPrice"],item["productMaxPrice"])
                if item["bulkDiscount"] != 'None' and item["bulkDiscount"] != '0':
                    bulkMinPrice = '%.2f'%(float(item["productMinPrice"])*(100-int(item["bulkDiscount"]))/100)
                    bulkMaxPrice = '%.2f'%(float(item["productMaxPrice"])*(100-int(item["bulkDiscount"]))/100)
                    bulkPrice = '%s-%s'%(bulkMinPrice,bulkMaxPrice)

                else:
                    bulkPrice = 'None'
            else:
                productPrice = item["productMinPrice"]
                if item["bulkDiscount"] != 'None' and item["bulkDiscount"] != '0':
                    bulkPrice = '%.2f'%(float(item["productMinPrice"])*(100-int(item["bulkDiscount"]))/100)
                else:
                    bulkPrice = 'None'


            if goodsStock and goodsStock != 'all':
                if int(stock) <= int(goodsStock):
                    productData.append({"storeId":item["STORE_ID"],"productId":item["productId"],"thumbnail":thumbnail,"productPrice":productPrice,"bulkDiscount":item["bulkDiscount"],"bulkOrder":item["bulkOrder"],"bulkPrice":bulkPrice,"offlineDate":wsOfflineDate,"leftTime":leftTime,"group":group,"template":template,"subject":item["subject"],"stock":stock})

            else:
                productData.append({"storeId":item["STORE_ID"],"productId":item["productId"],"thumbnail":thumbnail,"productPrice":productPrice,"bulkDiscount":item["bulkDiscount"],"bulkOrder":item["bulkOrder"],"bulkPrice":bulkPrice,"offlineDate":wsOfflineDate,"leftTime":leftTime,"group":group,"template":template,"subject":item["subject"],"stock":stock})


        currentPage = int(page)
        pageSize = 30
        totalCount = len(productData)
        productData = productData[(currentPage-1)*pageSize:currentPage*pageSize]

        mod = divmod(totalCount,pageSize)

        if mod[1]>0:
            totalPage = mod[0]+1
        else:
            totalPage = mod[0]

        pageList = range(1,totalPage+1)

        pageData = []

        for pg in pageList:
            if currentPage == pg:
                active = True
            else:
                active = False
            pageData.append({"page":pg,"active":active})


    else:
        msg = '您没有被授权查看店铺的产品'
        return render(request,'aside/error.html',locals())



    if request.method == 'POST':
        action = request.POST.get('action',None)
        #编辑SKU库存
        if action == '_editStock':
            skuData = []
            filterKey = ['csrfmiddlewaretoken','action','editStockProductId']
            productId = request.POST.get('editStockProductId',None)
            if productId and productId != '0':
                for key in request.POST:
                    if key not in filterKey:
                        print(user.id,productId,key,request.POST.get(key))
        #编辑SKU价格
        elif action == '_editPrice':
            filterKey = ['csrfmiddlewaretoken','action','editPriceProductId']
            productId = request.POST.get('editPriceProductId',None)
            if productId and productId != '0':
                for key in request.POST:
                    if key not in filterKey:
                        print(user.id,productId,key,request.POST.get(key))
        #编辑标题
        elif action == '_editSubject':
            filterKey = ['csrfmiddlewaretoken','action','editSubjectProductId']
            productId = request.POST.get('editSubjectProductId',None)
            if productId and productId != '0':
                fiedName = 'subject'
                fiedvalue = request.POST.get('editSubjectEn')

                print(user.id,productId,fiedName,fiedvalue)


    return render(request, 'aside/erp/goods-list.html', locals())



def getProductStock(request):
    data = dict()
    productId = request.GET.get('productId',None)
    if productId:
        sql = 'SELECT sum(ipmSkuStock) as stock FROM e_product_sku WHERE productId="%s"'%(productId)
        stockData = my_custom_sql(sql)
        stock = stockData[0]["stock"].split('.')[0]

        data["stock"] = stock
        data["productId"] = productId
        data["error"] = False
    else:
        data["error"] = True
        data["msg"] = u'产品ID有误！'.decode()

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')

def getProductSku(request):
    skuData = dict()
    productId = request.GET.get('productId',None)
    if productId:
        sql = 'SELECT skuId,skuPrice,ipmSkuStock FROM e_product_sku WHERE productId=%s'%(productId)
        temp = my_custom_sql(sql)

        if temp:
            skuData["error"] = 0
            if len(temp)>1:
                skuData["skuList"] = []
                skuData["items"] = len(temp)
                totalStock = 0
                for s in temp:
                    sku = dict()
                    sku["skuId"] = s["skuId"]
                    sku["skuPrice"] = s["skuPrice"]
                    sku["ipmSkuStock"] = s["ipmSkuStock"]
                    totalStock += int(s["ipmSkuStock"])
                    if s["skuId"] != '<none>':
                        sql = 'SELECT propertyValueDefinitionName,skuImage FROM `e_product_sku_property` WHERE skuId="%s" and productId="%s"'%(s["skuId"],productId)
                        kkk = my_custom_sql(sql)
                        sku["skuImage"] = kkk[0]["skuImage"]
                        sku["skuName"] = kkk[0]["propertyValueDefinitionName"]

                    skuData["skuList"].append(sku)

                skuData["totalStock"] = totalStock

            else:
                skuData["items"] = 1
                skuData["skuId"] = temp[0]["skuId"]
                skuData["skuPrice"] = temp[0]["skuPrice"]
                skuData["ipmSkuStock"] = int(temp[0]["ipmSkuStock"])
                skuData["totalStock"] = skuData["ipmSkuStock"]

        else:
            skuData["error"] = 1
            skuData["msg"] = u"无SKU数据".decode()

    else:
        skuData["msg"] = u"请传人产品ID".decode()
        skuData["error"] = 1
    return HttpResponse(json.dumps(skuData,ensure_ascii=False),content_type='application/json; charset=utf-8')


def getProductList(request):
    productData = dict()

    pageNo = request.POST.get('pageNo','1')
    pageSize = request.POST.get('pageSize','30')
    startDate = request.POST.get('startDate','0')
    endDate = request.POST.get('endDate','0')

    storeId = request.POST.get('store','0')

    response = HttpResponse(json.dumps(productData),content_type='application/json; charset=utf-8')
    response.set_cookie('pageSize',pageSize)
    return response


def mergeableOrder(request):
    if not request.user.is_authenticated():
        return redirect('/ucenter/login/'+"?from="+request.get_full_path())
    user = request.user

    sql = 'SELECT `store_id` from `sys_store_user` WHERE `user_id`="%d"'%(user.id)
    userStores = my_custom_sql(sql)

    sids = ''
    for s in userStores:
        sids +=s["store_id"]
        sids +=','
    sids = sids.strip(',')

    if sids == '':
        msg = '没有授权的店铺！'
        return render(request,'aside/error.html',locals())

    storeChildren = StoreInfo.objects.filter(store_id__in=sids.split(',')).values('children')

    d1 = datetime.datetime.now()
    d2 = d1 + datetime.timedelta(days=-90)
    endDate = d1.strftime('%Y-%m-%d')
    startDate = d2.strftime('%Y-%m-%d')

    if 'pageSize' in request.COOKIES:
        pageSize = request.COOKIES['pageSize']
    else:
        pageSize = '30'

    sql0 = 'select count(*) as totalNumber '
    sql = 'select `orderId`,`initOderAmount_amount`,`initOderAmount_currencyCode`,`sellerSignerFullname`,`memo`,`gmtPaySuccess`,`gmtCreate`,`orderStatus`,`buyerSignerFullname`,`buyerloginid`,`STORE_ID` '

    bar = 'from e_order a where (a.buyerloginid) in (select buyerloginid from e_order WHERE gmtPaySuccess is not null group by buyerloginid having count(*) > 1) and `loanStatus`="pay_success" '
    bar += ' AND STORE_ID in (%s) '%sids
    sql0 += bar
    sql += bar

    sql += 'order by gmtCreate DESC limit %s'%pageSize

    totalNum = my_custom_sql(sql0)[0]["totalNumber"]
    temp = my_custom_sql(sql)
    orderData = []

    for item in temp:
        #获取买家信息
        sql = 'SELECT `country` FROM e_order_receiptaddress WHERE orderId="%s"'%item["orderId"]
        foo = my_custom_sql(sql)
        countryCode = foo[0]["country"]
        country = countryTanslate(countryCode)
        #获取物流
        sql = 'SELECT orderId,logisticsServiceName,logisticsNo,gmtSend FROM e_order_logisticinfolist WHERE gmtSend is not null and orderId = %s '%(item["orderId"])
        foo = my_custom_sql(sql)
        if len(foo)>0:
            logistics = foo[0]['logisticsServiceName']
            logisticsNo = foo[0]['logisticsNo']
            #gmtSend = TransferDate(foo[0]['gmtSend'])
            gmtSend = foo[0]['gmtSend']
        else:
            logistics = ''
            logisticsNo = ''
            gmtSend = ''

        if item["gmtPaySuccess"] != None and item["gmtPaySuccess"] != 'None' and item["gmtPaySuccess"] != '':
            dd = TransferDate(item["gmtPaySuccess"])
            leftTime = dd + datetime.timedelta(days=6)
        else:
            leftTime = ''

        if item['memo'] != None and item['memo'] != '':
            md5 = md5Str(item['memo'])
            tmp = TranslateContent.objects.filter(md5=md5)
            if tmp.count()>0:
                memoTranslate = tmp[0].dst
            else:
                translator = TRANSLATOR()
                result = translator.baiduTranslate(item['memo'])
                if result.has_key('result'):
                    try:
                        TranslateContent.objects.create(md5=md5,src=item['memo'].replace('"','\\"'),dst=result["result"].replace('"','\\"'))
                    except:
                        pass
                    memoTranslate = result["result"]
                else:
                    print(result['msg'])
                    memoTranslate = ''
        else:
            memoTranslate = ''


        sql = 'SELECT SUM(productCount) as count,GROUP_CONCAT(DISTINCT productId) as productIds FROM `e_order_child` WHERE orderId="%s"'%item["orderId"]
        foo = my_custom_sql(sql)
        productCount = foo[0]["count"]
        bar = foo[0]["productIds"]

        bar = bar.split(',')

        imgList = []
        for i in bar:
            img = getThumbnail(i)
            imgList.append({'img':img,'productId':i})


        orderMemo = OrderMemo.objects.filter(order_id=item['orderId']).values('type').distinct()

        orderData.append({'orderId':item['orderId'],'storeID':item["STORE_ID"],'orderAmount':item['initOderAmount_amount'],'orderCurrency':item['initOderAmount_currencyCode'],'seller':item['sellerSignerFullname'],
                          'country':country,'countryCode':countryCode,'payTime':item['gmtPaySuccess'],'leftTime':str(leftTime),'sendTime':gmtSend,'logistics':logistics,'logisticsNo':logisticsNo,'orderStatus':transferOrderStatus(item['orderStatus']),
                          'memo':item['memo'],'memoTranslate':memoTranslate,'orderTime':item['gmtCreate'],'buyerName':item['buyerSignerFullname'],'buyerId':item['buyerloginid'],
                          'productCount':productCount,'imgList':imgList,'orderMemo':orderMemo})


    totalPage = int(totalNum) / int(pageSize)
    mod = int(totalNum) % int(pageSize)

    if mod>0:
        totalPage += 1


    pageList = range(1,totalPage+1)


    if totalPage>12:
        pageItems = pageList[:5] + ['...'] + pageList[totalPage-5:]
    else:
        pageItems = pageList

    pageData = []

    for pg in pageItems:
        if 1 == pg:
            active = True
        else:
            active = False
        if '...' == pg:
            link = '#'
        else:
            link = str(pg)
        pageData.append({"page":pg,'link':link,"active":active})

    currentItem = len(orderData)
    sql = 'SELECT YWCHANNEL_ID,`NAME`,`SERVICENAME`,`STATUS` FROM `y_channel` WHERE `ISUSER`=1'
    yanwenChannel = my_custom_sql(sql)

    return render(request, 'aside/erp/order-list-mergeable.html', locals())



def checkAddress(orderId):
    mark = True

    sql = 'SELECT `city`,`contactPerson`,`detailAddress`,`mobileNo`,`phoneNumber`,`phoneCountry`,`phoneArea`,`province`,`zip`,`country` FROM e_order_receiptaddress WHERE orderId="%s"'%orderId
    foo = my_custom_sql(sql)
    if len(foo)>0:
        address = foo[0]

        if address['country'] == 'None' or address['country'].strip()=='':
            mark = False
        if address['city'] == 'None' or address['city'].strip()=='':
            mark = False
        if address['contactPerson'] == 'None' or address['contactPerson'].strip()=='':
            mark = False
        if address['detailAddress'] == 'None' or address['detailAddress'].strip()=='':
            mark = False
        if address['mobileNo'] == 'None' or address['mobileNo'].strip()=='':
            if address['phoneNumber'] == 'None' or address['phoneNumber'].strip()=='':
                mark = False
    else:
        mark = False

    return mark


def checkDeclare(orderId):
    mark = 0
    sql = 'SELECT GROUP_CONCAT(DISTINCT productId) as productIds FROM `e_order_child` WHERE orderId="%s"'%orderId
    foo = my_custom_sql(sql)
    bar = foo[0]["productIds"]
    bar = bar.split(',')
    i = 0
    for p in bar:
        d = getDeclare(p)
        if len(d)>0:
            i += 1

    if i == len(bar):
        mark += 1

    return mark




def chekLogistics(orderId):
    sql = 'SELECT gmtSend FROM e_order_logisticinfolist WHERE gmtSend is not null and orderId="%s"'%orderId
    foo = my_custom_sql(sql)
    if len(foo)>0:
        return foo[0]['gmtSend']
    else:
        return 0


def checkOrderStatus(orderId):
    mark = 0
    sql = 'SELECT * FROM e_order_logisticinfolist WHERE gmtSend is not null and orderId="%s"'%orderId
    return mark



def getOrderDealMark(request):
    data = dict()
    orderId = request.GET.get('orderId',None)
    '''
    if orderId:
        odm = OrderDealMark.objects.filter(order_id=orderId)
        if odm.count()>0:
            data['result'] = {'address_check':odm[0].address_check,'declare_check':odm[0].declare_check,
                              'select_logicstics_time':str(odm[0].select_logicstics_time),'apply_logicstics_no_time':str(odm[0].apply_logicstics_no_time),
                              'virtual_delivery_time':str(odm[0].virtual_delivery_time),'print_logicstics_time':str(odm[0].print_logicstics_time),
                              'send_goods_time':str(odm[0].send_goods_time),'is_shelve':odm[0].is_shelve}
            data["error"] = False
        else:
            data["msg"] = u'订单未同步或无效的订单编号'.decode()
            data["error"] = True
    else:
        data["msg"] = u'非法请求'.decode()
        data["error"] = True
    '''

    if orderId:
        od = OrderDeal.objects.filter(orderId=orderId)
        if od.count()>0:
            data['result'] = {'address_check':od[0].addressCheck,'declare_check':od[0].declareCheck,
                              'select_logicstics_time':str(od[0].gmtSelectLogicstics),'apply_logicstics_no_time':str(od[0].gmtApplyLogicsticsNo),
                              'virtual_delivery_time':str(od[0].gmtVirtualDelivery),'print_logicstics_time':str(od[0].gmtPrintLogicstics),
                              'send_goods_time':str(od[0].gmtSendGoodsTime),'is_shelve':od[0].isShelve,'orderStatus':od[0].orderStatus}
            data["error"] = False
        else:
            data["msg"] = u'订单未同步或无效的订单编号'.decode()
            data["error"] = True
    else:
        data["msg"] = u'非法请求'.decode()
        data["error"] = True


    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def checkOrder(request):
    data = dict()
    mark = 0
    repetition = []
    sql = 'select orderId from e_order_deal where logisticsStatus="WAIT_SELLER_SEND_GOODS" group by contactPerson having count(*) >1'
    ttt = my_custom_sql(sql)
    for t in ttt:
        repetition.append(t['orderId'])

    orderId = request.GET.get('orderId',None)
    if orderId:
        orderId = orderId.split(',')
        for i in orderId:
            address_check = 0
            declare_check = 0
            if checkAddress(i):
                address_check = 1
            if checkDeclare(i)>0:
                declare_check = 1
            if chekLogistics(i):
                send_goods_time = chekLogistics(i)
            else:
                send_goods_time = None

            od = OrderDeal.objects.filter(orderId=orderId)
            if od.count()>0:
                od = od[0]
                od.addressCheck = address_check
                od.declareCheck = declare_check
                if send_goods_time:
                    od.gmtSendGoodsTime = send_goods_time

                if orderId in repetition:
                    od.isCanMergeOrder = 1
                od.save()
            else:
                mark +=1

    else:
        print('请传入ID')
        mark +=1



    if mark<1:
        data['msg'] = u'更新成功'.encode()
        data['error'] = False
    else:
        data['msg'] = u'%d个更新失败'%(mark).encode()
        data['error'] = True

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def getOrderTask(pageSize=10):
    sql = 'SELECT * FROM e_order WHERE isCheck=0 LIMIT %d'%pageSize
    return my_custom_sql(sql)


#1,获取总任务数，每次执行条数
def importOrder(request):
    data = dict()
    data['isComplete'] = True
    tasks = getOrderTask(10)
    repetition = []
    sql = 'select orderId from e_order_deal '
    sql += 'where  contactPerson in (select contactPerson from e_order_deal where gmtPaySuccess is not null group by contactPerson '
    sql += 'having count(contactPerson) > 1) AND logisticsStatus="WAIT_SELLER_SEND_GOODS" '
    ttt = custom_sql(sql)
    for t in ttt:
        repetition.append(t['orderId'])
    for item in tasks:

        #获取订单数据
        platform = 'aliexpress'
        orderId = item['orderId']
        storeId = item['STORE_ID']
        buyerSignerFullname = item['buyerSignerFullname'].replace('"','\\"')
        buyerloginId = item['buyerloginid']
        sellerOperatorLoginId = item['sellerOperatorLoginId']
        sellerSignerFullname = item['sellerSignerFullname'].replace('"','\\"')
        gmtCreate = item['gmtCreate']
        orderStatus = item['orderStatus']

        orderAmount = float(item['orderAmount_amount'])
        orderAmountCurrency = item['orderAmount_currencyCode']

        paymentType = item['paymentType']

        frozenStatus = item['frozenStatus']
        fundStatus = item['fundStatus']

        gmtModified = item['gmtModified']
        gmtPaySuccess = item['gmtPaySuccess']
        gmtTradeEnd = item['gmtTradeEnd']
        isPhone = item['isPhone']
        issueStatus = item['issueStatus']
        loanStatus = item['loanStatus']
        logisticsStatus = item['logisticsStatus']


        if item['MODIFY_DATE'] != None and item['MODIFY_DATE'] != 'None':
            gmtUpdate = item['MODIFY_DATE']
        else:
            gmtUpdate = item['CREATE_DATE']


        if item['leftSendGoodDay'] == 'None':
            leftDay = 0
        else:
            leftDay = int(item['leftSendGoodDay'])
        if item['leftSendGoodHour'] == 'None':
            leftHour = 0
        else:
            leftHour = int(item['leftSendGoodHour'])
        if item['leftSendGoodMin'] == 'None':
            leftMin = 0
        else:
            leftMin = int(item['leftSendGoodMin'])

        if leftDay != 'None' or leftHour != 'None' or leftMin != 'None':
            deadline = datetime.datetime.strptime(gmtUpdate,'%Y-%m-%d %H:%M:%S') + datetime.timedelta(days=leftDay,hours=leftHour,minutes=leftMin)
        else:
            deadline = datetime.datetime.strptime(gmtUpdate,'%Y-%m-%d %H:%M:%S')

        orderMemo = item['memo']

        #创建订单
        try:
            orderObject = OrderDeal.objects.create(orderId=orderId,platform=platform,storeId=storeId,buyerSignerFullname=buyerSignerFullname,buyerloginId=buyerloginId,
                                                   sellerOperatorLoginId=sellerOperatorLoginId,sellerSignerFullname=sellerSignerFullname,
                                                   orderStatus=orderStatus,gmtCreate=gmtCreate,orderAmount=orderAmount,orderAmountCurrency=orderAmountCurrency)
        except Exception as e:
            orderObject = OrderDeal.objects.get(orderId=orderId)

        if orderObject:
            orderObject.frozenStatus = frozenStatus
            orderObject.fundStatus = fundStatus
            orderObject.gmtModified = gmtModified
            orderObject.orderStatus = orderStatus


            if gmtPaySuccess != 'None' and gmtPaySuccess != '':
                orderObject.gmtPaySuccess = gmtPaySuccess
            if gmtTradeEnd != 'None' and gmtTradeEnd != '':
                orderObject.gmtTradeEnd = gmtTradeEnd
            if paymentType:
                orderObject.paymentType = paymentType
            orderObject.isPhone = int(isPhone)
            orderObject.issueStatus = issueStatus
            orderObject.loanStatus = loanStatus
            orderObject.logisticsStatus = logisticsStatus

            if orderMemo != 'None' and orderMemo != '':
                orderObject.orderMemo = orderMemo.replace('"','\\"')

            #检查收件人
            mark = True
            sql = 'SELECT `city`,`contactPerson`,`address2`,`detailAddress`,`mobileNo`,`phoneNumber`,`phoneCountry`,`phoneArea`,`province`,`zip`,`country` FROM e_order_receiptaddress WHERE orderId="%s"'%orderId
            foo = my_custom_sql(sql)
            if len(foo)>0:
                address = foo[0]

                if address['country'] == 'None' or address['country'].strip()=='':
                    mark = False
                if address['province'] == 'None' or address['province'].strip()=='':
                    mark = False
                if address['city'] == 'None' or address['city'].strip()=='':
                    mark = False
                if address['contactPerson'] == 'None' or address['contactPerson'].strip()=='':
                    mark = False
                if address['detailAddress'] == 'None' or address['detailAddress'].strip()=='':
                    mark = False
                if address['mobileNo'] == 'None' or address['mobileNo'].strip()=='':
                    if address['phoneNumber'] == 'None' or address['phoneNumber'].strip()=='':
                        mark = False

                bar = ''
                if address['address2'] == 'None' or address['address2'].strip()=='':
                    bar += address['address2']
                    bar += ','

                bar += address['detailAddress']
                bar += ','
                bar += address['city']
                bar += ','
                bar += address['province']
                bar += ','
                try:
                    ct = Country.objects.get(country_code=address['country'])
                    bar += ct.country_name
                    orderObject.country = ct.country_name_cn
                except:
                    bar += address['country']
                    orderObject.country = address['country']

                orderObject.deliveryAddress = bar.strip(',')
                orderObject.contactPerson = address['contactPerson']
                orderObject.countryCode = address['country']

            else:
                mark = False

            if mark:
                orderObject.addressCheck = 1
            else:
                orderObject.addressCheck = 0


            #检查报关信息
            mark = 0
            sql = 'SELECT GROUP_CONCAT(DISTINCT productId) as productIds,SUM(productCount) as count FROM `e_order_child` WHERE orderId="%s"'%orderId
            foo = my_custom_sql(sql)
            bar = foo[0]["productIds"]
            orderObject.orderProductsIds = bar
            orderObject.productCount = foo[0]["count"]
            bar = bar.split(',')
            i = 0
            for p in bar:
                d = getDeclare(p)
                if len(d)>0:
                    i += 1

            if i == len(bar):
                mark += 1

            if mark>0:
                orderObject.declareCheck = 1
            else:
                orderObject.declareCheck = 0

            #检查子订单、
            #获取用户指定物流
            sql = 'SELECT GROUP_CONCAT(DISTINCT logisticsType) as logisticsType FROM `e_order_child` WHERE orderId="%s"'%orderId
            foo = my_custom_sql(sql)
            bar = foo[0]["logisticsType"]
            bar = bar.split(',')
            baz = ''
            for j in bar:
                baz += translateLogistics(j)

            orderObject.buyersChooseLogistics = baz


            #获取发货物流
            sql = 'SELECT gmtReceived,gmtSend,logisticsNo,logisticsServiceName FROM e_order_logisticinfolist WHERE gmtSend is not null and orderId="%s"'%orderId
            foo = my_custom_sql(sql)
            deliveryLogistics = ''
            deliveryLogisticsNo = ''
            for i in foo:
                deliveryLogistics += i['logisticsServiceName']
                deliveryLogistics += ','
                deliveryLogisticsNo += i['logisticsNo']

            deliveryLogistics = deliveryLogistics.strip(',')
            deliveryLogisticsNo = deliveryLogisticsNo.strip(',')

            if deliveryLogistics:
                orderObject.deliveryLogistics = deliveryLogistics
            if deliveryLogisticsNo:
                orderObject.deliveryLogisticsNo = deliveryLogisticsNo
            if len(foo)>0:
                orderObject.gmtSendGoodsTime = foo[0]['gmtSend']
                if foo[0]['gmtReceived']!= 'None' and foo[0]['gmtReceived']!= '':
                    orderObject.gmtConfirmReceipt = foo[0]['gmtReceived']

            if orderId in repetition:
                orderObject.isCanMergeOrder = 1

            if orderStatus == 'WAIT_SELLER_SEND_GOODS' or orderStatus == 'SELLER_PART_SEND_GOODS':
                orderObject.dealStatus = 1
                orderObject.gmtSendGoodsDeadline = deadline
            else:
                orderObject.dealStatus = 0
            orderObject.gmtCheckTime=datetime.datetime.now()
            orderObject.save()

        #更新订单状态
        sql = 'UPDATE e_order SET isCheck=1 WHERE orderId="%s"'%item['orderId']
        my_update_sql(sql)


    #查询剩余的任务
    sql = 'SELECT count(*) as count FROM e_order WHERE isCheck=0'
    data['leftCount'] = int(my_custom_sql(sql)[0]['count'])
    if data['leftCount'] > 0:
        data['isComplete'] = False

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')



def order(request):
    if not request.user.is_authenticated():
        return redirect('/ucenter/login/'+"?from="+request.get_full_path())
    user = request.user

    status = request.GET.get('status',None)
    send = request.GET.get('send',None)
    orderMsg = request.GET.get('orderMsg','0')

    sql = 'SELECT `store_id` from `sys_store_user` WHERE `user_id`="%d"'%(user.id)
    userStores = my_custom_sql(sql)

    sids = ''
    for s in userStores:
        sids +=s["store_id"]
        sids +=','
    sids = sids.strip(',')


    if sids == '':
        msg = '没有授权的店铺！'
        return render(request,'aside/error.html',locals())

    storeChildren = StoreInfo.objects.filter(store_id__in=sids.split(',')).values('children')

    d1 = datetime.datetime.now()
    d2 = d1 + datetime.timedelta(days=-29)
    endDate = d1.strftime('%Y-%m-%d')
    startDate = d2.strftime('%Y-%m-%d')

    if 'pageSize' in request.COOKIES:
        pageSize = request.COOKIES['pageSize']
    else:
        pageSize = '30'

    sql0 = 'SELECT count(*) as totalNumber FROM `e_order`'
    sql = 'SELECT `orderId`,`initOderAmount_amount`,`initOderAmount_currencyCode`,`sellerSignerFullname`,`memo`,convert_tz(`gmtPaySuccess`,"+08:00","-08:00") as gmtPaySuccess,convert_tz(`gmtCreate`,"+08:00","-08:00") as gmtCreate,`orderStatus`,`buyerSignerFullname`,`buyerloginid`,`STORE_ID` FROM `e_order`'
    #sql += ' WHERE not ISNULL(`gmtPaySuccess`) '
    #sql0 += ' WHERE not ISNULL(`gmtPaySuccess`) '
    sql += ' WHERE not ISNULL(`initOderAmount_amount`) '
    sql0 += ' WHERE not ISNULL(`initOderAmount_amount`) '

    sql += ' AND STORE_ID in (%s) '%sids
    sql0 += ' AND STORE_ID in (%s) '%sids


    if status:
        sql += ' AND fundStatus = "%s" '%status
        sql0 += ' AND fundStatus = "%s" '%status

    if send:
        sql += ' AND logisticsStatus = "%s" '%send
        sql0 += ' AND logisticsStatus = "%s" '%send

    if orderMsg != '0' :
        sql += ' AND (memo <>" ") '
        sql0 += ' AND (memo <>" ") '

    if startDate:
        sql0 += 'AND gmtCreate >"%s" '%startDate
        sql += 'AND gmtCreate >"%s" '%startDate
    sql += ' ORDER BY `gmtCreate` DESC '
    sql += ' limit %s'%pageSize

    totalNum = my_custom_sql(sql0)[0]["totalNumber"]
    temp = my_custom_sql(sql)
    orderData = []

    for item in temp:
        #获取买家信息
        sql = 'SELECT `country` FROM e_order_receiptaddress WHERE orderId="%s"'%item["orderId"]
        foo = my_custom_sql(sql)
        countryCode = foo[0]["country"]
        country = countryTanslate(countryCode)
        #获取物流
        sql = 'SELECT logisticsServiceName,logisticsNo,gmtSend,logisticsTypeCode,receiveStatus FROM e_order_logisticinfolist WHERE gmtSend is not null and orderId = %s '%(item["orderId"])
        foo = my_custom_sql(sql)
        orderLogistics = foo

        if len(foo)>0:
            logistics = foo[0]['logisticsServiceName']
            logisticsNo = foo[0]['logisticsNo']
            #gmtSend = TransferDate(foo[0]['gmtSend'])
            gmtSend = foo[0]['gmtSend']
        else:
            logistics = ''
            logisticsNo = ''
            gmtSend = ''

        if item["gmtPaySuccess"] != None and item["gmtPaySuccess"] != 'None' and item["gmtPaySuccess"] != '':
            dd = TransferDate(item["gmtPaySuccess"])
            leftTime = dd + datetime.timedelta(days=6)
        else:
            leftTime = ''

        if item['memo'] != None and item['memo'] != '':
            md5 = md5Str(item['memo'])
            tmp = TranslateContent.objects.filter(md5=md5)
            if tmp.count()>0:
                memoTranslate = tmp[0].dst
            else:
                translator = TRANSLATOR()
                result = translator.baiduTranslate(item['memo'])
                if result.has_key('result'):
                    try:
                        TranslateContent.objects.create(md5=md5,src=item['memo'].replace('"','\\"'),dst=result["result"].replace('"','\\"'))
                    except:
                        pass
                    memoTranslate = result["result"]
                else:
                    print(result['msg'])
                    memoTranslate = ''
        else:
            memoTranslate = ''


        #sql = 'SELECT COUNT(*) as count FROM `e_order_child` WHERE orderId="%s"'%item["orderId"]
        #sql = 'SELECT SUM(productCount) as count,GROUP_CONCAT(DISTINCT productId) as productIds FROM `e_order_child` WHERE orderId="%s"'%item["orderId"]
        sql = 'SELECT orderId,lotNum,productAttributes,productCount,productId,productName,productUnit,skuCode,initOrderAmt_amount,initOrderAmt_currencyCode,productPrie_amount,logisticsType FROM e_order_child WHERE orderId="%s" ORDER BY initOrderAmt_amount DESC '%item["orderId"]
        foo = my_custom_sql(sql)
        productCount = 0
        imgList = []

        for i in foo:
            productCount += int(i['productCount'])
            productAttributes = json.loads(i['productAttributes'])
            sku = productAttributes['sku']
            productId = i['productId']
            if sku:
                x = 0
                img = ''

                for j in sku:
                    if j['skuImg'] != '':
                        img = '//ae01.alicdn.com/kf/'+j['skuImg']
                    else:
                        if x <1:
                            img = getThumbnail(productId)
                    x +=1
            else:
                img = getThumbnail(productId)

            imgList.append({'img':img,'productId':productId})


        orderMemo = OrderMemo.objects.filter(order_id=item['orderId']).values('type').distinct()

        orderData.append({'orderId':item['orderId'],'storeID':item["STORE_ID"],'orderAmount':item['initOderAmount_amount'],'orderCurrency':item['initOderAmount_currencyCode'],'seller':item['sellerSignerFullname'],
                          'country':country,'countryCode':countryCode,'payTime':item['gmtPaySuccess'],'leftTime':str(leftTime),'sendTime':gmtSend,'logistics':logistics,'logisticsNo':logisticsNo,'orderStatus':transferOrderStatus(item['orderStatus']),
                          'memo':item['memo'],'memoTranslate':memoTranslate,'orderTime':item['gmtCreate'],'buyerName':item['buyerSignerFullname'],'buyerId':item['buyerloginid'],
                          'productCount':productCount,'imgList':imgList,'orderMemo':orderMemo,'orderLogistics':orderLogistics,'logisticsData':getOrderLogistics(item['orderId'])})


    totalPage = int(totalNum) / int(pageSize)
    mod = int(totalNum) % int(pageSize)

    if mod>0:
        totalPage += 1


    pageList = range(1,totalPage+1)


    if totalPage>12:
        pageItems = pageList[:5] + ['...'] + pageList[totalPage-5:]
    else:
        pageItems = pageList

    pageData = []

    for pg in pageItems:
        if 1 == pg:
            active = True
        else:
            active = False
        if '...' == pg:
            link = '#'
        else:
            link = str(pg)
        pageData.append({"page":pg,'link':link,"active":active})

    currentItem = len(orderData)


    sql = 'SELECT count(*) as count FROM e_order WHERE isCheck=0'
    totalTask = my_custom_sql(sql)[0]['count']


    #sql = 'SELECT YWCHANNEL_ID,`NAME`,`STATUS` FROM `y_channel`'
    #sql = 'SELECT LOGISTICSL_CHANNEL_ID as YWCHANNEL_ID ,`NAME` FROM `e_logisticsl_channel`'
    sql = 'SELECT YWCHANNEL_ID,`NAME`,`SERVICENAME`,`STATUS` FROM `y_channel` WHERE `ISUSER`=1 and `enabled`=1'
    yanwenChannel = my_custom_sql(sql)

    #sql = 'SELECT ID,displayNameCN,`serviceName` FROM `e_express_channel` WHERE `enabled`=1'
    sql = 'SELECT id,diyName,warehouseName,logisticsServiceId,logisticsServiceName FROM `e_smt_logistics_channel` WHERE `enabled`=1'
    smtChannel = my_custom_sql(sql)


    return render(request, 'aside/erp/order-list-all.html', locals())


def orderIndex(request):
    if not request.user.is_authenticated():
        return redirect('/ucenter/login/'+"?from="+request.get_full_path())
    user = request.user

    status = request.GET.get('status',None)
    send = request.GET.get('send',None)
    orderMsg = request.GET.get('orderMsg','0')
    addressCheck = request.GET.get('addressCheck',None)
    declareCheck = request.GET.get('declareCheck',None)
    isOutOfStock = request.GET.get('isOutOfStock',None)
    noMatchingSku = request.GET.get('noMatchingSku',None)
    isCanMergeOrder = request.GET.get('isCanMergeOrder',None)
    isShelve = request.GET.get('isShelve',None)
    noSelectLog = request.GET.get('noSelectLog',None)
    applying = request.GET.get('applying',None)
    waitPrint = request.GET.get('waitPrint',None)

    dealStatus = 0

    actionMemoMark = 0

    sql = 'SELECT `store_id` from `sys_store_user` WHERE `user_id`="%d"'%(user.id)
    userStores = my_custom_sql(sql)

    sids = ''
    for s in userStores:
        sids +=s["store_id"]
        sids +=','
    sids = sids.strip(',')


    if sids == '':
        msg = '没有授权的店铺！'
        return render(request,'aside/error.html',locals())

    storeChildren = StoreInfo.objects.filter(store_id__in=sids.split(',')).values('children')

    d1 = datetime.datetime.now()
    d2 = d1 + datetime.timedelta(days=-29)
    endDate = d1.strftime('%Y-%m-%d')
    startDate = d2.strftime('%Y-%m-%d')

    if 'pageSize' in request.COOKIES:
        pageSize = request.COOKIES['pageSize']
    else:
        pageSize = '30'

    sql0 = 'SELECT count(*) as totalNumber FROM `e_order_deal`'
    sql = 'SELECT `orderId`,`orderAmount`,`orderAmountCurrency`,`sellerSignerFullname`,`orderMemo`,convert_tz(gmtPaySuccess,"+08:00","-08:00") as gmtPaySuccess,convert_tz(`gmtCreate`,"+08:00","-08:00") as gmtCreate,`orderStatus`,`buyerSignerFullname`,`buyerloginId`,`storeId`,`gmtSendDelivery`,`gmtSendGoodsDeadline`,`gmtVirtualDelivery`,`gmtPrintLogicstics` FROM `e_order_deal`'
    sql += ' WHERE not ISNULL(`orderAmount`) '
    sql0 += ' WHERE not ISNULL(`orderAmount`) '


    sql += ' AND storeId in (%s) '%sids
    sql0 += ' AND storeId in (%s) '%sids


    if send:
        sql += ' AND logisticsStatus = "%s" '%send
        sql0 += ' AND logisticsStatus = "%s" '%send

    if orderMsg != '0':
        sql += ' AND (orderMemo <>" ") '
        sql0 += ' AND (orderMemo <>" ") '
        status = 'WAIT_SELLER_SEND_GOODS'
        dealStatus = 1
        actionMemoMark += 1

    if addressCheck:
        sql += ' AND addressCheck = %s '%addressCheck
        sql0 += ' AND addressCheck = %s '%addressCheck
        isShelve = 0
        dealStatus = 1
        actionMemoMark += 1

    if declareCheck:
        sql += ' AND declareCheck = %s '%declareCheck
        sql0 += ' AND declareCheck = %s '%declareCheck
        isShelve = 0
        dealStatus = 1
        actionMemoMark += 1

    if isOutOfStock:
        sql += ' AND isOutOfStock = %s '%isOutOfStock
        sql0 += ' AND isOutOfStock = %s '%isOutOfStock
        isShelve = 0
        actionMemoMark += 1

    if noMatchingSku:
        sql += ' AND noMatchingSku = %s '%noMatchingSku
        sql0 += ' AND noMatchingSku = %s '%noMatchingSku
        isShelve = 0
        dealStatus = 1
        actionMemoMark += 1

    if isCanMergeOrder:
        sql += ' AND isCanMergeOrder = %s '%isCanMergeOrder
        sql0 += ' AND isCanMergeOrder = %s '%isCanMergeOrder
        isShelve = 0
        dealStatus = 1
        actionMemoMark += 1

    if isShelve:
        sql += ' AND isShelve = %s '%isShelve
        sql0 += ' AND isShelve = %s '%isShelve
        actionMemoMark += 1

    if noSelectLog:
        sql += ' AND isnull(gmtSelectLogicstics) '
        sql0 += ' AND isnull(gmtSelectLogicstics) '
        status = 'WAIT_SELLER_SEND_GOODS'


    if status:
        sql += ' AND orderStatus = "%s" '%status
        sql0 += ' AND orderStatus = "%s" '%status


    if applying:
        if applying == '0':
            dealStatus = 2
        elif applying == '1':
            dealStatus = 3
        elif applying == '2':
            dealStatus = 9


    if waitPrint:
        dealStatus = 4

    if dealStatus:
        sql += ' AND dealStatus = %s '%dealStatus
        sql0 += ' AND dealStatus = %s '%dealStatus


    if startDate:
        sql0 += 'AND gmtCreate >"%s" '%startDate
        sql += 'AND gmtCreate >"%s" '%startDate


    sql += ' ORDER BY `gmtCreate` DESC '
    sql += ' limit %s'%pageSize

    #print(sql0)
    totalNum = custom_sql(sql0)[0]["totalNumber"]
    temp = custom_sql(sql)
    orderData = []

    for item in temp:
        #获取买家信息
        sql = 'SELECT `country` FROM e_order_receiptaddress WHERE orderId="%s"'%item["orderId"]
        foo = my_custom_sql(sql)
        if len(foo)>0:
            countryCode = foo[0]["country"]
            country = countryTanslate(countryCode)
        else:
            countryCode = ''
            country = ''
        #获取物流
        sql = 'SELECT logisticsServiceName,logisticsNo,gmtSend,logisticsTypeCode,receiveStatus FROM e_order_logisticinfolist WHERE gmtSend is not null and orderId = %s '%(item["orderId"])
        foo = my_custom_sql(sql)
        orderLogistics = foo

        if len(foo)>0:
            logistics = foo[0]['logisticsServiceName']
            logisticsNo = foo[0]['logisticsNo']
            #gmtSend = TransferDate(foo[0]['gmtSend'])
            gmtSend = foo[0]['gmtSend']
        else:
            logistics = ''
            logisticsNo = ''
            gmtSend = ''

        '''
        if item["gmtPaySuccess"] != None and item["gmtPaySuccess"] != 'None' and item["gmtPaySuccess"] != '':
            dd = TransferDate(item["gmtPaySuccess"])
            leftTime = dd + datetime.timedelta(days=6)
        else:
            leftTime = ''
        '''

        if item['gmtSendGoodsDeadline'] != 'None':
            leftTime = item['gmtSendGoodsDeadline']
        else:
            leftTime = ''

        if item['orderMemo'] != None and item['orderMemo'] != '' and item['orderMemo'] != 'None':
            md5 = md5Str(item['orderMemo'])
            tmp = TranslateContent.objects.filter(md5=md5)
            if tmp.count()>0:
                memoTranslate = tmp[0].dst
            else:
                translator = TRANSLATOR()
                result = translator.baiduTranslate(item['orderMemo'])
                if result.has_key('result'):
                    try:
                        TranslateContent.objects.create(md5=md5,src=item['orderMemo'].replace('"','\\"'),dst=result["result"].replace('"','\\"'))
                    except:
                        pass
                    memoTranslate = result["result"]
                else:
                    print(result['msg'])
                    memoTranslate = ''
        else:
            memoTranslate = ''


        #sql = 'SELECT COUNT(*) as count FROM `e_order_child` WHERE orderId="%s"'%item["orderId"]
        #sql = 'SELECT SUM(productCount) as count,GROUP_CONCAT(DISTINCT productId) as productIds FROM `e_order_child` WHERE orderId="%s"'%item["orderId"]
        sql = 'SELECT orderId,lotNum,productAttributes,productCount,productId,productName,productUnit,skuCode,initOrderAmt_amount,initOrderAmt_currencyCode,productPrie_amount,logisticsType FROM e_order_child WHERE orderId="%s" ORDER BY initOrderAmt_amount DESC '%item["orderId"]
        foo = my_custom_sql(sql)
        productCount = 0
        imgList = []

        for i in foo:
            productCount += int(i['productCount'])
            productAttributes = json.loads(i['productAttributes'])
            sku = productAttributes['sku']
            productId = i['productId']
            if sku:
                x = 0
                img = ''

                for j in sku:
                    if j['skuImg'] != '':
                        img = '//ae01.alicdn.com/kf/'+j['skuImg']
                    else:
                        if x <1:
                            img = getThumbnail(productId)
                    x +=1
            else:
                img = getThumbnail(productId)

            imgList.append({'img':img,'productId':productId})


        orderMemo = OrderMemo.objects.filter(order_id=item['orderId']).values('type').distinct()

        orderData.append({'orderId':item['orderId'],'storeID':item["storeId"],'orderAmount':item['orderAmount'],'orderCurrency':item['orderAmountCurrency'],'seller':item['sellerSignerFullname'],
                          'country':country,'countryCode':countryCode,'payTime':item['gmtPaySuccess'],'leftTime':str(leftTime),'sendTime':gmtSend,'deliveryTime':item['gmtSendDelivery'],'virtualTime':item['gmtVirtualDelivery'],'printTime':item['gmtPrintLogicstics'],'logistics':logistics,'logisticsNo':logisticsNo,'orderStatus':transferOrderStatus(item['orderStatus']),
                          'memo':item['orderMemo'],'memoTranslate':memoTranslate,'orderTime':item['gmtCreate'],'buyerName':item['buyerSignerFullname'],'buyerId':item['buyerloginId'],
                          'productCount':productCount,'imgList':imgList,'orderMemo':orderMemo,'orderLogistics':orderLogistics,'logisticsData':getOrderLogistics(item['orderId'])})


    totalPage = int(totalNum) / int(pageSize)
    mod = int(totalNum) % int(pageSize)

    if mod>0:
        totalPage += 1


    pageList = range(1,totalPage+1)


    if totalPage>12:
        pageItems = pageList[:5] + ['...'] + pageList[totalPage-5:]
    else:
        pageItems = pageList

    pageData = []

    for pg in pageItems:
        if 1 == pg:
            active = True
        else:
            active = False
        if '...' == pg:
            link = '#'
        else:
            link = str(pg)
        pageData.append({"page":pg,'link':link,"active":active})

    currentItem = len(orderData)


    sql = 'SELECT count(*) as count FROM e_order WHERE isCheck=0'
    totalTask = my_custom_sql(sql)[0]['count']


    #sql = 'SELECT YWCHANNEL_ID,`NAME`,`STATUS` FROM `y_channel`'
    #sql = 'SELECT LOGISTICSL_CHANNEL_ID as YWCHANNEL_ID ,`NAME` FROM `e_logisticsl_channel`'
    sql = 'SELECT YWCHANNEL_ID,`NAME`,`SERVICENAME`,`STATUS` FROM `y_channel` WHERE `ISUSER`=1 and `enabled`=1'
    yanwenChannel = my_custom_sql(sql)

    #sql = 'SELECT ID,displayNameCN,`serviceName` FROM `e_express_channel` WHERE `enabled`=1'
    sql = 'SELECT id,diyName,warehouseName,logisticsServiceId,logisticsServiceName FROM `e_smt_logistics_channel` WHERE `enabled`=1'
    smtChannel = my_custom_sql(sql)


    return render(request, 'aside/erp/order-list.html', locals())

def transferOrderStatus(status):
    s = ''
    if status == 'PLACE_ORDER_SUCCESS':
        s += u'等待付款'
    elif status == 'IN_CANCEL':
        s += u'买家申请取消'
    elif status == 'WAIT_SELLER_SEND_GOODS':
        s += u'等待发货'
    elif status == 'SELLER_PART_SEND_GOODS':
        s += u'部分发货'
    elif status == 'WAIT_BUYER_ACCEPT_GOODS':
        s += u'等待收货'
    elif status == 'FUND_PROCESSING':
        s += u'资金处理中'
    elif status == 'IN_ISSUE':
        s += u'纠纷中'
    elif status == 'IN_FROZEN':
        s += u'冻结中'
    elif status == 'WAIT_SELLER_EXAMINE_MONEY':
        s += u'等待确认金额'
    elif status == 'RISK_CONTROL':
        s += u'风控中'
    elif status == 'FINISH':
        s += u'已结束'
    return s


def translateLogistics(src):
    dist = ''
    sql = 'SELECT displayNameCN FROM e_express_channel WHERE serviceName="%s" OR logisticsCompany="%s" OR displayName="%s"'%(src,src,src)
    foo = my_custom_sql(sql)
    if len(foo)>0:
        dist += foo[0]['displayNameCN']
    else:
        dist += src
    return dist


def checkOrderLogistics(request):
    data = dict()
    if not request.user.is_authenticated():
        data['error'] = True
        data['msg'] = u'无权限访问！'.decode()
    else:
        orderId = request.GET.get('orderId',None)
        if orderId and orderId != '':
            data['error'] = False
            data['sendLogistics'] = dict()
            data['deliveryLogistics'] = dict()
            data['chooseLogistics'] = dict()
            #查询平台的订单发货物流信息表
            sql = 'SELECT logisticsServiceName,logisticsNo,gmtSend,logisticsTypeCode,receiveStatus FROM e_order_logisticinfolist WHERE gmtSend is not null and orderId = %s '%(orderId)
            foo = my_custom_sql(sql)

            data['sendLogistics']['Logistics'] = foo
            data['sendLogistics']['count'] = len(foo)

            data['deliveryLogistics']['Logistics'] = []
            delivery = DeliveryInformation.objects.filter(order_id=orderId,is_children=0).values('channel','channel_code','logicstics_no','create_time','submit_time','send_time','logicstics_file','lpNumber')
            for item in delivery:
                data['deliveryLogistics']['Logistics'].append({'channel':item['channel'],'channel_code':item['channel_code'],'logicstics_no':item['logicstics_no'],'lpNumber':item['lpNumber'],'create_time':str(item['create_time']),'logicstics_file':item['logicstics_file']})
            data['deliveryLogistics']['count'] = len(delivery)

            data['chooseLogistics']['Logistics'] = []
            chooseChannel = ChooseLogistics.objects.filter(order_id=orderId,available=1).values('channel_id','channel','channel_code','choose_time','logicstics_no','lpNumber','channel_type')
            for item in chooseChannel:
                data['chooseLogistics']['Logistics'].append({'channel_id':item['channel_id'],'channel':item['channel'],'channel_code':item['channel_code'],'choose_time':str(item['choose_time']),'logicstics_no':item['logicstics_no'],'lpNumber':item['lpNumber'],'channel_type':item['channel_type']})
            data['chooseLogistics']['count'] = len(chooseChannel)
        else:
            data['error'] = True
            data['msg'] = u'非法请求！'.decode()

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')



def getOrderDealList(request):
    orderData = dict()
    pageNo = request.GET.get('pageNo','1')
    pageSize = request.GET.get('pageSize','30')
    startDate = request.GET.get('startDate','0')
    endDate = request.GET.get('endDate','0')

    storeId = request.GET.get('store','0')
    country = request.GET.get('country','0')

    searchType = request.GET.get('searchType','1')
    keyword = request.GET.get('keyword',None)

    status = request.GET.get('status',None)
    send = request.GET.get('send',None)

    orderMsg = request.GET.get('orderMsg','0')



    if not request.user.is_authenticated():
        orderData["msg"] = u'没有授权的店铺！'
        orderData["error"] = True
    else:
        user = request.user

        sql = 'SELECT `store_id` from `sys_store_user` WHERE `user_id`="%d"'%(user.id)
        userStores = my_custom_sql(sql)

        sids = ''
        for s in userStores:
            sids +=s["store_id"]
            sids +=','
        sids = sids.strip(',')

        if sids == '':
            orderData["msg"] = u'没有授权的店铺！'
            orderData["error"] = True
        else:

            #sql0 = 'SELECT count(*) as totalNumber FROM `e_order` left join e_order_buyerinfo on e_order.orderId = e_order_buyerinfo.orderId WHERE not ISNULL(`initOderAmount_amount`) '
            #sql = 'SELECT e_order.`orderId`,`initOderAmount_amount`,`initOderAmount_currencyCode`,`sellerSignerFullname`,' \
            #      '`memo`,`gmtPaySuccess`,`gmtCreate`,`buyerSignerFullname`,`buyerloginid`,`orderStatus`,e_order_buyerinfo.country as country,`STORE_ID` FROM `e_order` left join e_order_buyerinfo on e_order.orderId = e_order_buyerinfo.orderId WHERE not ISNULL(`initOderAmount_amount`) '

            sql0 = 'SELECT count(*) as totalNumber FROM `e_order`'
            sql = 'SELECT `orderId`,`initOderAmount_amount`,`initOderAmount_currencyCode`,`sellerSignerFullname`,`memo`,convert_tz(`gmtPaySuccess`,"+08:00","-08:00") as gmtPaySuccess,convert_tz(`gmtCreate`,"+08:00","-08:00") as gmtCreate,`orderStatus`,`buyerSignerFullname`,`buyerloginid`,`STORE_ID` FROM `e_order`'

            sql += ' WHERE not ISNULL(`initOderAmount_amount`) '
            sql0 += ' WHERE not ISNULL(`initOderAmount_amount`) '

            sql += ' AND STORE_ID in (%s) '%sids
            sql0 += ' AND STORE_ID in (%s) '%sids

            if status != 'PAY_SUCCESS':
                sql += ' AND fundStatus = "%s" '%status
                sql0 += ' AND fundStatus = "%s" '%status

            if send != '0' :
                sql += ' AND logisticsStatus = "%s" '%send
                sql0 += ' AND logisticsStatus = "%s" '%send

            if orderMsg != '0' :
                sql += ' AND (memo <>" ") '
                sql0 += ' AND (memo <>" ") '


            if storeId != '0':
                sql0 += 'AND sellerSignerFullname="%s" '%storeId
                sql += 'AND sellerSignerFullname="%s" '%storeId

            if country != '0':
                sql0 += 'AND e_order_buyerinfo.country="%s" '%country
                sql += 'AND e_order_buyerinfo.country="%s" '%country


            if startDate != '0':
                sql0 += 'AND gmtCreate > "%s" '%startDate
                sql += 'AND gmtCreate > "%s" '%startDate

            if endDate != '0':
                sql0 += 'AND gmtCreate < "%s" '%endDate
                sql += 'AND gmtCreate < "%s" '%endDate


            if keyword:
                keyword = keyword.replace('"','\\"')
                if searchType == '1':
                    sql0 += 'AND e_order.`orderId` like "%'+keyword+'%" '
                    sql += 'AND e_order.`orderId` like "%'+keyword+'%" '
                elif searchType == '2':
                    sql0 += 'AND e_order.`orderId` like "%'+keyword+'%" '
                    sql += 'AND e_order.`orderId` like "%'+keyword+'%" '
                elif searchType == '3':
                    sql0 += 'AND e_order.`orderId` like "%'+keyword+'%" '
                    sql += 'AND e_order.`orderId` like "%'+keyword+'%" '
                elif searchType == '4':
                    sql0 += 'AND e_order.`orderId` like "%'+keyword+'%" '
                    sql += 'AND e_order.`orderId` like "%'+keyword+'%" '
                elif searchType == '5':
                    sql0 += 'AND buyerloginid like "%'+keyword+'%" '
                    sql += 'AND buyerloginid like "%'+keyword+'%" '
                elif searchType == '6':
                    sql0 += 'AND buyerSignerFullname like "%'+keyword+'%" '
                    sql += 'AND buyerSignerFullname like "%'+keyword+'%" '


            sql0 += ' ORDER BY `gmtCreate` DESC '
            sql += 'ORDER BY `gmtCreate` DESC '

            #print(sql0)
            totalCount = my_custom_sql(sql0)

            sql += ' limit %d,%d'%((int(pageNo)-1)*int(pageSize),int(pageSize))
            temp = my_custom_sql(sql)

            orderData["OrderList"] = []

            for item in temp:
                #获取订单国家信息
                #sql = 'SELECT `country`,`firstName`,`lastName` FROM `e_order_buyerinfo` WHERE `orderId`="%s" limit 1'%(item["orderId"])
                sql = 'SELECT `country` FROM e_order_receiptaddress WHERE orderId="%s"'%item["orderId"]
                foo = my_custom_sql(sql)
                countryCode = foo[0]["country"]
                country = countryTanslate(countryCode)

                #获取物流
                sql = 'SELECT logisticsServiceName,logisticsNo,gmtSend,logisticsTypeCode,receiveStatus FROM e_order_logisticinfolist WHERE gmtSend is not null and orderId = %s '%(item["orderId"])
                foo = my_custom_sql(sql)
                orderLogistics = foo

                isMultiplePackage  = False

                if len(foo)>0:
                    logistics = foo[0]['logisticsServiceName']
                    logisticsNo = foo[0]['logisticsNo']
                    gmtSend = foo[0]['gmtSend']
                    if len(foo)>1:
                        isMultiplePackage  = True
                else:
                    logistics = ''
                    logisticsNo = ''
                    gmtSend = ''

                if item['memo'] != None and item['memo'] != '':
                    md5 = md5Str(item['memo'])
                    tmp = TranslateContent.objects.filter(md5=md5)
                    if tmp.count()>0:
                        memoTranslate = tmp[0].dst
                    else:
                        translator = TRANSLATOR()
                        result = translator.baiduTranslate(item['memo'])
                        if result.has_key('result'):
                            try:
                                TranslateContent.objects.create(md5=md5,src=item['memo'].replace('"','\\"'),dst=result["result"].replace('"','\\"'))
                            except:
                                pass
                            memoTranslate = result["result"]
                        else:
                            print(result['msg'])
                            memoTranslate = ''
                else:
                    memoTranslate = ''

                if item["gmtPaySuccess"] != None and item["gmtPaySuccess"] != 'None' and item["gmtPaySuccess"] != '':

                    dd = TransferDate(item["gmtPaySuccess"])
                    leftTime = dd + datetime.timedelta(days=6)
                else:
                    leftTime = ''

                #sql = 'SELECT COUNT(*) as count FROM `e_order_child` WHERE orderId="%s"'%item["orderId"]
                #sql = 'SELECT SUM(productCount) as count,GROUP_CONCAT(DISTINCT productId) as productIds FROM `e_order_child` WHERE orderId="%s"'%item["orderId"]
                sql = 'SELECT orderId,lotNum,productAttributes,productCount,productId,productName,productUnit,skuCode,initOrderAmt_amount,initOrderAmt_currencyCode,productPrie_amount,logisticsType FROM e_order_child WHERE orderId="%s" ORDER BY initOrderAmt_amount DESC '%item["orderId"]
                foo = my_custom_sql(sql)

                productCount = 0
                imgList = []

                for i in foo:
                    productCount += int(i['productCount'])
                    productAttributes = json.loads(i['productAttributes'])
                    sku = productAttributes['sku']
                    productId = i['productId']
                    if sku:
                        x = 0
                        img = ''

                        for j in sku:
                            if j['skuImg'] != '':
                                img = '//ae01.alicdn.com/kf/'+j['skuImg']
                            else:
                                if x <1:
                                    img = getThumbnail(productId)
                            x +=1
                    else:
                        img = getThumbnail(productId)

                    imgList.append({'img':img,'productId':productId})


                orderMemo = OrderMemo.objects.filter(order_id=item['orderId']).values('type').distinct()


                mm = list()
                if orderMemo.count()>0:
                    for i in orderMemo:
                        mm.append({'type':int(i['type'])})


                orderData["OrderList"].append({'orderId':item['orderId'],'storeID':item['STORE_ID'],'orderAmount':item['initOderAmount_amount'],'orderCurrency':item['initOderAmount_currencyCode'],'seller':item['sellerSignerFullname'],
                                  'country':country,'countryCode':countryCode,'payTime':item['gmtPaySuccess'],'leftTime':str(leftTime),'sendTime':gmtSend,'logistics':logistics,'logisticsNo':logisticsNo,'orderStatus':transferOrderStatus(item['orderStatus']),
                                  'memo':item['memo'],'memoTranslate':memoTranslate,'orderTime':item['gmtCreate'],'buyerName':item['buyerSignerFullname'],'buyerId':item['buyerloginid'],
                                  'productCount':productCount,'imgList':imgList,'orderMemo':mm,'orderLogistics':orderLogistics,'isMultiplePackage':isMultiplePackage,'logisticsData':getOrderLogistics(item['orderId'])})


            orderData["TotalNumber"] = int(totalCount[0]["totalNumber"])
            orderData["CurrentPage"] = int(pageNo)


            totalPage = int(totalCount[0]["totalNumber"]) / int(pageSize)
            mod = int(totalCount[0]["totalNumber"]) % int(pageSize)

            if mod>0:
                totalPage += 1

            orderData["TotalPage"] = totalPage

            orderData["pageData"] = []

            if orderData["CurrentPage"] >= 9:
                if orderData["CurrentPage"]<= totalPage-9:
                    pageList = range(orderData["CurrentPage"]-3,orderData["CurrentPage"]+4)
                    pageItems = [1,'...'] + pageList + ['...',totalPage]
                else:
                    pageList = range(totalPage-7,totalPage+1)
                    pageItems = [1,'...'] + pageList
            else:
                if totalPage <=12:
                    pageItems = range(1,totalPage+1)
                else:
                    pageList = range(1,10)
                    pageItems = pageList + ['...',totalPage]


            orderData["PageItem"] = '<li><a href="javascript:"><i class="fa fa-chevron-left"></i></a></li>'

            for pg in pageItems:
                html = '<li'
                if orderData["CurrentPage"] == pg:
                    active = True
                    html += ' class="active"'
                else:
                    active = False

                html += '>'
                html += '<a href="javascript:" data-val="%s" class="pageItem">%s</a></li>'%(pg,pg)

                if '...' == pg:
                    link = '#'
                else:
                    link = str(pg)

                orderData["pageData"].append({"page":pg,'link':link,"active":active})

                orderData["PageItem"] += html


            orderData["PageItem"] += '<li><a href="javascript:"><i class="fa fa-chevron-right"></i></a></li>'

            num0 = len(orderData["OrderList"])

            if num0 >0:
                i0 = int(pageSize)*(int(pageNo)-1)+1
                i1 = int(pageSize)*(int(pageNo)-1)+len(orderData["OrderList"])
            else:
                i0 = 0
                i1 = 0

            orderData["PageTxt"] = u'第%d-%d条，共%d条'.decode()%(i0,i1,orderData["TotalNumber"])

            orderData["error"] = False

    response = HttpResponse(json.dumps(orderData,ensure_ascii=False),content_type='application/json; charset=utf-8')
    response.set_cookie('pageSize',pageSize)
    return response


def getOrderList(request):
    orderData = dict()
    pageNo = request.GET.get('pageNo','1')
    pageSize = request.GET.get('pageSize','30')
    startDate = request.GET.get('startDate','0')
    endDate = request.GET.get('endDate','0')

    storeId = request.GET.get('store','0')
    country = request.GET.get('country','0')

    searchType = request.GET.get('searchType','1')
    keyword = request.GET.get('keyword',None)


    status = request.GET.get('status',None)
    send = request.GET.get('send',None)
    orderMsg = request.GET.get('orderMsg','0')
    addressCheck = request.GET.get('addressCheck',None)
    declareCheck = request.GET.get('declareCheck',None)
    isOutOfStock = request.GET.get('isOutOfStock',None)
    noMatchingSku = request.GET.get('noMatchingSku',None)
    isCanMergeOrder = request.GET.get('isCanMergeOrder',None)
    isShelve = request.GET.get('isShelve',None)
    noSelectLog = request.GET.get('noSelectLog',None)
    applying = request.GET.get('applying',None)
    waitPrint = request.GET.get('waitPrint',None)

    dealStatus = 0

    actionMemoMark = 0


    if not request.user.is_authenticated():
        orderData["msg"] = u'没有授权的店铺！'
        orderData["error"] = True
    else:
        user = request.user

        sql = 'SELECT `store_id` from `sys_store_user` WHERE `user_id`="%d"'%(user.id)
        userStores = my_custom_sql(sql)

        sids = ''
        for s in userStores:
            sids +=s["store_id"]
            sids +=','
        sids = sids.strip(',')

        if sids == '':
            orderData["msg"] = u'没有授权的店铺！'
            orderData["error"] = True
        else:

            #sql0 = 'SELECT count(*) as totalNumber FROM `e_order` left join e_order_buyerinfo on e_order.orderId = e_order_buyerinfo.orderId WHERE not ISNULL(`initOderAmount_amount`) '
            #sql = 'SELECT e_order.`orderId`,`initOderAmount_amount`,`initOderAmount_currencyCode`,`sellerSignerFullname`,' \
            #      '`memo`,`gmtPaySuccess`,`gmtCreate`,`buyerSignerFullname`,`buyerloginid`,`orderStatus`,e_order_buyerinfo.country as country,`STORE_ID` FROM `e_order` left join e_order_buyerinfo on e_order.orderId = e_order_buyerinfo.orderId WHERE not ISNULL(`initOderAmount_amount`) '

            sql0 = 'SELECT count(*) as totalNumber FROM `e_order_deal`'
            sql = 'SELECT `orderId`,`orderAmount`,`orderAmountCurrency`,`sellerSignerFullname`,`orderMemo`,convert_tz(`gmtPaySuccess`,"+08:00","-08:00") as gmtPaySuccess,convert_tz(`gmtCreate`,"+08:00","-08:00") as gmtCreate,`orderStatus`,`buyerSignerFullname`,`buyerloginId`,`storeId`,gmtSendDelivery,gmtSendGoodsDeadline FROM `e_order_deal`'

            sql += ' WHERE not ISNULL(`orderAmount`) '
            sql0 += ' WHERE not ISNULL(`orderAmount`) '

            sql += ' AND storeId in (%s) '%sids
            sql0 += ' AND storeId in (%s) '%sids


            if send != '0' :
                sql += ' AND logisticsStatus = "%s" '%send
                sql0 += ' AND logisticsStatus = "%s" '%send

            if orderMsg != '0' :
                sql += ' AND (orderMemo <>" ") '
                sql0 += ' AND (orderMemo <>" ") '

            if addressCheck:
                sql += ' AND addressCheck = %s '%addressCheck
                sql0 += ' AND addressCheck = %s '%addressCheck
                isShelve = 0
                dealStatus = 1
                actionMemoMark += 1

            if declareCheck:
                sql += ' AND declareCheck = %s '%declareCheck
                sql0 += ' AND declareCheck = %s '%declareCheck
                isShelve = 0
                dealStatus = 1
                actionMemoMark += 1

            if isOutOfStock:
                sql += ' AND isOutOfStock = %s '%isOutOfStock
                sql0 += ' AND isOutOfStock = %s '%isOutOfStock
                isShelve = 0
                actionMemoMark += 1

            if noMatchingSku:
                sql += ' AND noMatchingSku = %s '%noMatchingSku
                sql0 += ' AND noMatchingSku = %s '%noMatchingSku
                isShelve = 0
                dealStatus = 1
                actionMemoMark += 1

            if isCanMergeOrder:
                sql += ' AND isCanMergeOrder = %s '%isCanMergeOrder
                sql0 += ' AND isCanMergeOrder = %s '%isCanMergeOrder
                isShelve = 0
                dealStatus = 1
                actionMemoMark += 1

            if isShelve:
                sql += ' AND isShelve = %s '%isShelve
                sql0 += ' AND isShelve = %s '%isShelve
                actionMemoMark += 1

            if noSelectLog:
                sql += ' AND isnull(gmtSelectLogicstics) '
                sql0 += ' AND isnull(gmtSelectLogicstics) '
                status = 'WAIT_SELLER_SEND_GOODS'


            if status:
                sql += ' AND orderStatus = "%s" '%status
                sql0 += ' AND orderStatus = "%s" '%status


            if applying:
                if applying == '0':
                    dealStatus = 2
                elif applying == '1':
                    dealStatus = 3


            if waitPrint:
                dealStatus = 4

            if dealStatus:
                sql += ' AND dealStatus = %s '%dealStatus
                sql0 += ' AND dealStatus = %s '%dealStatus


            if storeId != '0':
                sql0 += 'AND sellerSignerFullname="%s" '%storeId
                sql += 'AND sellerSignerFullname="%s" '%storeId

            if country != '0':
                sql0 += 'AND countryCode="%s" '%country
                sql += 'AND countryCode="%s" '%country


            if startDate != '0':
                sql0 += 'AND gmtCreate > "%s" '%startDate
                sql += 'AND gmtCreate > "%s" '%startDate

            if endDate != '0':
                sql0 += 'AND gmtCreate < "%s" '%endDate
                sql += 'AND gmtCreate < "%s" '%endDate


            if keyword:
                keyword = keyword.replace('"','\\"')
                if searchType == '1':
                    sql0 += 'AND `orderId` like "%'+keyword+'%" '
                    sql += 'AND `orderId` like "%'+keyword+'%" '
                elif searchType == '2':
                    sql0 += 'AND `PackageNo` like "%'+keyword+'%" '
                    sql += 'AND `PackageNo` like "%'+keyword+'%" '
                elif searchType == '3':
                    sql0 += 'AND `deliveryLogisticsNo` like "%'+keyword+'%" '
                    sql += 'AND `deliveryLogisticsNo` like "%'+keyword+'%" '
                elif searchType == '4':
                    sql0 += 'AND `orderProductsIds` like "%'+keyword+'%" '
                    sql += 'AND `orderProductsIds` like "%'+keyword+'%" '
                elif searchType == '5':
                    sql0 += 'AND buyerloginid like "%'+keyword+'%" '
                    sql += 'AND buyerloginid like "%'+keyword+'%" '
                elif searchType == '6':
                    sql0 += 'AND buyerSignerFullname like "%'+keyword+'%" '
                    sql += 'AND buyerSignerFullname like "%'+keyword+'%" '


            sql0 += ' ORDER BY `gmtCreate` DESC '
            sql += 'ORDER BY `gmtCreate` DESC '

            #print(sql0)
            totalCount = custom_sql(sql0)

            sql += ' limit %d,%d'%((int(pageNo)-1)*int(pageSize),int(pageSize))
            temp = custom_sql(sql)

            orderData["OrderList"] = []

            for item in temp:

                #获取订单国家信息
                #sql = 'SELECT `country`,`firstName`,`lastName` FROM `e_order_buyerinfo` WHERE `orderId`="%s" limit 1'%(item["orderId"])
                sql = 'SELECT `country` FROM e_order_receiptaddress WHERE orderId="%s"'%item["orderId"]
                foo = my_custom_sql(sql)
                if len(foo)>0:
                    countryCode = foo[0]["country"]
                    country = countryTanslate(countryCode)
                else:
                    countryCode = ''
                    country = ''
                #获取物流
                sql = 'SELECT logisticsServiceName,logisticsNo,gmtSend,logisticsTypeCode,receiveStatus FROM e_order_logisticinfolist WHERE gmtSend is not null and orderId = %s '%(item["orderId"])
                foo = my_custom_sql(sql)
                orderLogistics = foo

                isMultiplePackage  = False

                if len(foo)>0:
                    logistics = foo[0]['logisticsServiceName']
                    logisticsNo = foo[0]['logisticsNo']
                    gmtSend = foo[0]['gmtSend']
                    if len(foo)>1:
                        isMultiplePackage  = True
                else:
                    logistics = ''
                    logisticsNo = ''
                    gmtSend = ''

                if item['orderMemo'] != None and item['orderMemo'] != '' and item['orderMemo'] != 'None':
                    md5 = md5Str(item['orderMemo'])
                    tmp = TranslateContent.objects.filter(md5=md5)
                    if tmp.count()>0:
                        memoTranslate = tmp[0].dst
                    else:
                        translator = TRANSLATOR()
                        result = translator.baiduTranslate(item['orderMemo'])
                        if result.has_key('result'):
                            try:
                                TranslateContent.objects.create(md5=md5,src=item['orderMemo'].replace('"','\\"'),dst=result["result"].replace('"','\\"'))
                            except:
                                pass
                            memoTranslate = result["result"]
                        else:
                            print(result['msg'])
                            memoTranslate = ''
                else:
                    memoTranslate = ''

                '''
                if item["gmtPaySuccess"] != None and item["gmtPaySuccess"] != 'None' and item["gmtPaySuccess"] != '':

                    dd = TransferDate(item["gmtPaySuccess"])
                    leftTime = dd + datetime.timedelta(days=6)
                else:
                    leftTime = ''
                '''

                if item['gmtSendGoodsDeadline'] != 'None':
                    leftTime = item['gmtSendGoodsDeadline']
                else:
                    leftTime = ''

                #sql = 'SELECT COUNT(*) as count FROM `e_order_child` WHERE orderId="%s"'%item["orderId"]
                #sql = 'SELECT SUM(productCount) as count,GROUP_CONCAT(DISTINCT productId) as productIds FROM `e_order_child` WHERE orderId="%s"'%item["orderId"]
                sql = 'SELECT orderId,lotNum,productAttributes,productCount,productId,productName,productUnit,skuCode,initOrderAmt_amount,initOrderAmt_currencyCode,productPrie_amount,logisticsType FROM e_order_child WHERE orderId="%s" ORDER BY initOrderAmt_amount DESC '%item["orderId"]
                foo = my_custom_sql(sql)

                productCount = 0
                imgList = []

                for i in foo:
                    productCount += int(i['productCount'])
                    productAttributes = json.loads(i['productAttributes'])
                    sku = productAttributes['sku']
                    productId = i['productId']
                    if sku:
                        x = 0
                        img = ''

                        for j in sku:
                            if j['skuImg'] != '':
                                img = '//ae01.alicdn.com/kf/'+j['skuImg']
                            else:
                                if x <1:
                                    img = getThumbnail(productId)
                            x +=1
                    else:
                        img = getThumbnail(productId)

                    imgList.append({'img':img,'productId':productId})


                orderMemo = OrderMemo.objects.filter(order_id=item['orderId']).values('type').distinct()


                mm = list()
                if orderMemo.count()>0:
                    for i in orderMemo:
                        mm.append({'type':int(i['type'])})


                orderData["OrderList"].append({'orderId':item['orderId'],'storeID':item['storeId'],'orderAmount':item['orderAmount'],'orderCurrency':item['orderAmountCurrency'],'seller':item['sellerSignerFullname'],
                                  'country':country,'countryCode':countryCode,'payTime':item['gmtPaySuccess'],'leftTime':str(leftTime),'sendTime':gmtSend,'deliveryTime':item['gmtSendDelivery'],'logistics':logistics,'logisticsNo':logisticsNo,'orderStatus':transferOrderStatus(item['orderStatus']),
                                  'memo':item['orderMemo'],'memoTranslate':memoTranslate,'orderTime':item['gmtCreate'],'buyerName':item['buyerSignerFullname'],'buyerId':item['buyerloginId'],
                                  'productCount':productCount,'imgList':imgList,'orderMemo':mm,'orderLogistics':orderLogistics,'isMultiplePackage':isMultiplePackage,'logisticsData':getOrderLogistics(item['orderId'])})


            orderData["TotalNumber"] = int(totalCount[0]["totalNumber"])
            orderData["CurrentPage"] = int(pageNo)


            totalPage = int(totalCount[0]["totalNumber"]) / int(pageSize)
            mod = int(totalCount[0]["totalNumber"]) % int(pageSize)

            if mod>0:
                totalPage += 1

            orderData["TotalPage"] = totalPage

            orderData["pageData"] = []

            if orderData["CurrentPage"] >= 9:
                if orderData["CurrentPage"]<= totalPage-9:
                    pageList = range(orderData["CurrentPage"]-3,orderData["CurrentPage"]+4)
                    pageItems = [1,'...'] + pageList + ['...',totalPage]
                else:
                    pageList = range(totalPage-7,totalPage+1)
                    pageItems = [1,'...'] + pageList
            else:
                if totalPage <=12:
                    pageItems = range(1,totalPage+1)
                else:
                    pageList = range(1,10)
                    pageItems = pageList + ['...',totalPage]


            orderData["PageItem"] = '<li><a href="javascript:"><i class="fa fa-chevron-left"></i></a></li>'

            for pg in pageItems:
                html = '<li'
                if orderData["CurrentPage"] == pg:
                    active = True
                    html += ' class="active"'
                else:
                    active = False

                html += '>'
                html += '<a href="javascript:" data-val="%s" class="pageItem">%s</a></li>'%(pg,pg)

                if '...' == pg:
                    link = '#'
                else:
                    link = str(pg)

                orderData["pageData"].append({"page":pg,'link':link,"active":active})

                orderData["PageItem"] += html


            orderData["PageItem"] += '<li><a href="javascript:"><i class="fa fa-chevron-right"></i></a></li>'

            num0 = len(orderData["OrderList"])

            if num0 >0:
                i0 = int(pageSize)*(int(pageNo)-1)+1
                i1 = int(pageSize)*(int(pageNo)-1)+len(orderData["OrderList"])
            else:
                i0 = 0
                i1 = 0

            orderData["PageTxt"] = u'第%d-%d条，共%d条'.decode()%(i0,i1,orderData["TotalNumber"])

            orderData["error"] = False

    response = HttpResponse(json.dumps(orderData,ensure_ascii=False),content_type='application/json; charset=utf-8')
    response.set_cookie('pageSize',pageSize)
    return response


def getOrderLogistics(orderId):
    data = dict()
    data['sendLogistics'] = dict()
    data['deliveryLogistics'] = dict()
    data['chooseLogistics'] = dict()
    #平台发货物流信息
    sql = 'SELECT logisticsServiceName,logisticsNo,gmtSend,logisticsTypeCode,receiveStatus FROM e_order_logisticinfolist WHERE gmtSend is not null and orderId = %s '%(orderId)
    foo = my_custom_sql(sql)
    data['sendLogistics']['Logistics'] = foo
    data['sendLogistics']['count'] = len(foo)

    #系统发货包裹物流
    data['deliveryLogistics']['Logistics'] = []
    delivery = DeliveryInformation.objects.filter(order_id=orderId,is_children=0).values('channel','channel_code','logicstics_no','create_time','submit_time','send_time','logicstics_file','lpNumber')
    for item in delivery:
        data['deliveryLogistics']['Logistics'].append({'channel':item['channel'],'channel_code':item['channel_code'],'logicstics_no':item['logicstics_no'],'create_time':str(item['create_time']),'logicstics_file':item['logicstics_file'],'lpNumber':item['lpNumber']})
    data['deliveryLogistics']['count'] = len(delivery)

    #选择物流
    data['chooseLogistics']['Logistics'] = []
    chooseChannel = ChooseLogistics.objects.filter(order_id=orderId,available=1).values('channel_id','channel','channel_code','choose_time','logicstics_no','lpNumber','channel_type','is_submit','isError','errorMsg')
    for item in chooseChannel:
        data['chooseLogistics']['Logistics'].append({'channel_id':item['channel_id'],'channel':item['channel'],'channel_code':item['channel_code'],'choose_time':str(item['choose_time']),'lpNumber':item['lpNumber'],'logicstics_no':item['logicstics_no'],'channel_type':item['channel_type'],'is_submit':item['is_submit'],'isError':item['isError'],'errorMsg':item['errorMsg']})
    data['chooseLogistics']['count'] = len(chooseChannel)

    return data


def getDeclare(productId):
    data = dict()
    d = ProductDeclare.objects.filter(product_id__contains=productId)
    if d.count()>0:
        data["chinese_name"] = d[0].chinese_name
        data["english_name"] = d[0].english_name
        data["amount"] = str(d[0].amount)
        data["weight"] = str(d[0].weight)
        data["currency"] = str(d[0].currency)
    return data


def getOrderDeclare(orderId):
    result = []
    sql = 'SELECT DISTINCT productId from e_order_child WHERE orderId="%s" '%orderId
    foo = my_custom_sql(sql)
    sumAmount = 10.0
    num = len(foo)
    for item in foo:
        data = dict()
        d = ProductDeclare.objects.filter(product_id__contains=item['productId'])
        if d.count()>0:
            data["NameCh"] = d[0].chinese_name
            data["NameEn"] = d[0].english_name
            if d[0].amount > sumAmount/num:
                data["DeclaredValue"] = '%.2f'%(sumAmount/num)
            else:
                data["DeclaredValue"] = str(d[0].amount)
            data["Weight"] = str(d[0].weight)
            data["DeclaredCurrency"] = str(d[0].currency)
            result.append(data)

    return result

def getSmtDeclare(orderId):
    result = []
    sql = 'SELECT DISTINCT productId from e_order_child WHERE orderId="%s" '%orderId
    foo = my_custom_sql(sql)
    sumAmount = 10.0
    num = len(foo)
    for item in foo:
        data = dict()
        d = ProductDeclare.objects.filter(product_id__contains=item['productId'])
        sql = 'SELECT sum(productCount) as productCount FROM e_order_child WHERE orderId="%s" AND productId="%s"'%(orderId,item['productId'])
        productCount = my_custom_sql(sql)[0]['productCount']
        if d.count()>0:
            data['productId'] = int(item['productId'])
            data["categoryCnDesc"] = d[0].chinese_name
            data["categoryEnDesc"] = d[0].english_name
            data["productNum"] = int(productCount)
            if d[0].amount > sumAmount/num:
                data["productDeclareAmount"] = int(sumAmount/num)
            else:
                data["productDeclareAmount"] = int(d[0].amount)
            data["productWeight"] =  round(float(int(d[0].weight))/1000,3)
            data["isContainsBattery"] = 0
            data["isAneroidMarkup"] = 0
            data["isOnlyBattery"] = 0
            result.append(data)
    return result



def getDeliveryMemo(orderId):
    result = ''
    memo = OrderMemo.objects.filter(order_id=orderId,type=1)
    for item in memo:
        result += item.content
    return result

def editProductDeclare(request):
    data = dict()
    if not request.user.is_authenticated():
        data['error'] = True
        data['msg'] = u'无权限访问！'.decode()
    else:
        orderId = request.GET.get('orderId',None)
        type = request.GET.get('type','0')
        productId = request.GET.get('productId',None)
        cName = request.GET.get('cName',None)
        eName = request.GET.get('eName',None)
        amount = request.GET.get('amount',None)
        weight = request.GET.get('weight',None)
        code = request.GET.get('code',None)

        if type=='0':
            if productId and cName and eName and amount and weight:
                pd = ProductDeclare.objects.filter(order_id=orderId,product_id__contains=productId)
                updateCount = 0
                if pd.count()>0:
                    updateCount += pd.count()
                    if code and code.strip():
                        pd.update(chinese_name=cName,english_name=eName,amount=float(amount),weight=int(weight),code=code)
                    else:
                        pd.update(chinese_name=cName,english_name=eName,amount=float(amount),weight=int(weight),code=code)
                else:
                    updateCount += 1
                    if code and code.strip():
                        ProductDeclare.objects.create(order_id=orderId,product_id=productId,chinese_name=cName,english_name=eName,amount=float(amount),weight=int(weight),code=code)
                    else:
                        ProductDeclare.objects.create(order_id=orderId,product_id=productId,chinese_name=cName,english_name=eName,amount=float(amount),weight=int(weight))

                data['error'] = False
                data['updateCount'] = updateCount
            else:
                data['error'] = True
                data['msg'] = u'非法请求'.decode()

        else:
            if productId and cName and eName and amount and weight:
                pd = ProductDeclare.objects.filter(product_id__contains=productId)
                updateCount = 0
                if pd.count()>0:
                    updateCount += pd.count()
                    if code and code.strip():
                        pd.update(chinese_name=cName,english_name=eName,amount=float(amount),weight=int(weight),code=code)
                    else:
                        pd.update(chinese_name=cName,english_name=eName,amount=float(amount),weight=int(weight),code=code)
                else:
                    updateCount += 1
                    if code and code.strip():
                        ProductDeclare.objects.create(product_id=productId,chinese_name=cName,english_name=eName,amount=float(amount),weight=int(weight),code=code)
                    else:
                        ProductDeclare.objects.create(product_id=productId,chinese_name=cName,english_name=eName,amount=float(amount),weight=int(weight))

                data['error'] = False
                data['updateCount'] = updateCount
            else:
                data['error'] = True
                data['msg'] = u'非法请求'.decode()


        #更新本订单的申报信息状态

        m = checkDeclare(orderId)
        if m > 0:
            try:
                #odm = OrderDealMark.objects.get(order_id=orderId)
                #odm.declare_check = 1
                #odm.save()
                od = OrderDeal.objects.get(orderId=orderId)
                od.declareCheck = 1
                od.save()
            except:
                print('订单未审核')

        #更新本产品相关订单的申报状态
        sql = 'SELECT orderId FROM e_order_child WHERE productId="%s"'%productId
        pids = my_custom_sql(sql)
        for item in pids:
            m = checkDeclare(item['orderId'])
            if m > 0:
                try:
                    #odm = OrderDealMark.objects.get(order_id=item['orderId'])
                    #odm.declare_check = 1
                    #odm.save()
                    od = OrderDeal.objects.get(orderId=item['orderId'])
                    od.declareCheck = 1
                    od.save()
                except:
                    print('订单未审核')

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


@csrf_exempt
def addOrderMemo(request):
    data = dict()
    if not request.user.is_authenticated():
        data['error'] = True
        data['msg'] = u'无权限访问！'.decode()
    else:
        user_id = request.user.id
        if request.method != 'POST':
            data['error'] = True
            data['msg'] = u'非法提交'.decode()
        else:

            orderId = request.POST.get('memoOrderID','None')
            memoType = request.POST.get('memoType',None)
            memoContent = request.POST.get('memoContent',None)

            if orderId != 'None' and memoType and memoContent:
                memoContent = memoContent.replace('"','\\"')
                OrderMemo.objects.create(user_id=int(user_id),type=int(memoType),order_id=orderId,content=memoContent)
                data['error'] = False
            else:
                data['error'] = True
                data['msg'] = u'订单号、备注类型及备注内容必须填写完整！'.decode()

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


@csrf_exempt
def selectLogistics(request):
    data = dict()
    if not request.user.is_authenticated():
        data['error'] = True
        data['msg'] = u'无权限访问！'.decode()
    else:
        if request.method != 'POST':
            data['error'] = True
            data['msg'] = u'非法提交'.decode()
        else:
            orderId = request.POST.get('orderId','None')
            channelId = request.POST.get('channelId',None)
            channelName = request.POST.get('channelName',None)
            channelCode = request.POST.get('channelCode',None)
            channelType = request.POST.get('channelType',None)
            #print(channelId,channelName,channelCode,channelType)
            if orderId != 'None' and channelId and channelName and channelCode and channelType:
                try:
                    cl = ChooseLogistics.objects.get(order_id=orderId)
                    cl.channel_id = channelId
                    cl.channel_type = channelType
                    cl.channel = channelName
                    cl.channel_code = channelCode
                    cl.choose_time = datetime.datetime.now()
                    cl.save()



                except:
                    cl = ChooseLogistics.objects.create(order_id=orderId,channel_id=channelId,channel=channelName,channel_code=channelCode,channel_type=channelType,choose_time = datetime.datetime.now())
                    data['error'] = False
                    data['msg'] = u'物流选择成功'.decode()

                    data['error'] = False
                    data['msg'] = u'物流更新成功'.decode()

                try:
                    od = OrderDeal.objects.get(orderId=orderId)
                    od.gmtSelectLogicstics = cl.choose_time
                    od.dealStatus = 2
                    od.save()

                    #odm = OrderDealMark.objects.get(order_id=orderId)
                    #odm.select_logicstics_time = cl.choose_time
                    #odm.save()

                except Exception as e:
                    data['error'] = True
                    data['msg'] = unicode(str(e)).decode()

            else:
                data['error'] = True
                data['msg'] = u'参数或内容不完整'.decode()


    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def genPackageNo():
    #N：正常，R：换面单，R：重发
    pn = 'XYN00 000 0000000'
    random_digital(4)+'-'+random_str(4)
    return pn

def createLogistics(request):
    data = dict()
    if not request.user.is_authenticated():
        data['error'] = True
        data['msg'] = u'无权限访问！'.decode()
    else:
        if request.method != 'POST':
            data['error'] = True
            data['msg'] = u'非法提交'.decode()
        else:
            orderId = request.POST.get('orderId','None')
            channelId = request.POST.get('channelId',None)
            channelName = request.POST.get('channelName',None)
            channelCode = request.POST.get('channelCode',None)
            channelType = request.POST.get('channelType',None)
            if orderId != 'None' and channelId and channelName and channelCode and channelType:
                try:
                    packageNo = genPackageNo()
                    cl = ChooseLogistics.objects.get(order_id=orderId)
                    cl.channel_id = channelId
                    cl.channel_type = channelType
                    cl.channel = channelName
                    cl.channel_code = channelCode
                    cl.choose_time = datetime.datetime.now()
                    cl.save()



                except:
                    cl = ChooseLogistics.objects.create(order_id=orderId,channel_id=channelId,channel=channelName,channel_code=channelCode,channel_type=channelType,choose_time = datetime.datetime.now())
                    data['error'] = False
                    data['msg'] = u'物流选择成功'.decode()

                    data['error'] = False
                    data['msg'] = u'物流更新成功'.decode()

                try:
                    od = OrderDeal.objects.get(orderId=orderId)
                    od.gmtSelectLogicstics = cl.choose_time
                    od.dealStatus = 2
                    od.save()

                    #odm = OrderDealMark.objects.get(order_id=orderId)
                    #odm.select_logicstics_time = cl.choose_time
                    #odm.save()

                except Exception as e:
                    data['error'] = True
                    data['msg'] = unicode(str(e)).decode()

            else:
                data['error'] = True
                data['msg'] = u'参数或内容不完整'.decode()

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def testSend(request):
    data = dict()
    storeId = request.GET.get('storeId',None)
    orderId = request.GET.get('orderId',None)
    isVirtual = request.GET.get('isVirtual','1')

    if storeId and orderId:
        cl = ChooseLogistics.objects.get(order_id=orderId)
        od = OrderDeal.objects.get(orderId=orderId)
        if cl.channel_type == 'yanwen':
            serviceName = 'YANWEN'
        elif cl.channel_type == 'aliexpress':
            serviceName = 'YANWEN_AM'
        logisticsNo = cl.logicstics_no
        sendType = 'all'
        trackingWebsite = ''

        od.gmtVirtualDelivery = datetime.datetime.now()
        od.save()

        data['error'] = True
        data['msg'] = u"成功".encode()
    else:
        data['error'] = True
        data['msg'] = "缺少参数"
    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def getChooseLogistics(orderId):
    data = dict()
    #选择物流
    data['Logistics'] = []
    chooseChannel = ChooseLogistics.objects.filter(order_id=orderId,available=1).values('channel_id','channel','channel_code','choose_time','logicstics_no','lpNumber','channel_type','is_submit','isError','errorMsg')
    for item in chooseChannel:
        data['Logistics'].append({'channel_id':item['channel_id'],'channel':item['channel'],'channel_code':item['channel_code'],'choose_time':str(item['choose_time']),'lpNumber':item['lpNumber'],'logicstics_no':item['logicstics_no'],'channel_type':item['channel_type'],'is_submit':item['is_submit'],'isError':item['isError'],'errorMsg':item['errorMsg']})
    data['count'] = len(chooseChannel)
    return data


def sendDelivery(request):
    data = dict()
    if not request.user.is_authenticated():
        data['success'] = False
        data['msg'] = u'无权限访问！'.decode()
    else:
        orderId = request.GET.get('orderId',None)
        storeId = request.GET.get('storeId',None)
        sendType = '1'
        #print(orderId,storeId)
        chooseLogistics = getChooseLogistics(orderId)
        od = OrderDeal.objects.get(orderId=orderId)
        if od.gmtVirtualDelivery == None or od.gmtVirtualDelivery == '':
            #先发货，调用发货
            od.gmtVirtualDelivery = datetime.datetime.now()
            od.save()
        try:
            di = DeliveryInformation.objects.create(type=int(sendType),order_id=orderId,store_id=int(storeId),
                                               channel=chooseLogistics['Logistics'][0]['channel'],channel_code=chooseLogistics['Logistics'][0]['channel_code'],
                                               channel_type=chooseLogistics['Logistics'][0]['channel_type'],logicstics_no=chooseLogistics['Logistics'][0]['logicstics_no'],
                                               lpNumber=chooseLogistics['Logistics'][0]['lpNumber'],quantity=od.productCount,submit_time=od.gmtVirtualDelivery,
                                               create_time=datetime.datetime.now())
            od.gmtSendDelivery = di.create_time
            od.orderStatus = 'WAIT_BUYER_ACCEPT_GOODS'
            od.logisticsStatus = 'SELLER_SEND_GOODS'
            od.dealStatus = 5
            od.save()
            data['success'] = True
        except Exception as e:
            print(e)
            data['success'] = False
            data['msg'] = u'发货失败！'.decode()

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def printMb(request):
    data = dict()
    if not request.user.is_authenticated():
        data['success'] = False
        data['msg'] = u'无权限访问！'.decode()
    else:
        orderId = request.GET.get('orderId',None)
        pdf = request.GET.get('pdf',None)
        if orderId:
            od = OrderDeal.objects.get(orderId=orderId)
            od.gmtPrintLogicstics = datetime.datetime.now()
            od.save()
            data['success'] = True
        else:
            data['success'] = False
            data['msg'] = u'订单号有误！'.decode()

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')

def applyLogisticsNo(request):
    data = dict()
    if not request.user.is_authenticated():
        data['success'] = False
        data['msg'] = u'无权限访问！'.decode()
    else:
        orderId = request.GET.get('orderId','None')
        if orderId and orderId.strip() != '':
            #1,获取选择的物流，2,判断物流类型：yanwen:燕文，aliexpress：速卖通
            #3.1，燕文调用线下发货接口
            #3.2，拼接线下发货所需json数据
            #3.3，提交
            #3.4，更新选择物流选择表中状态，已提交
            #3.5，更新订单处理表中的申请单号时间
            try:
                cl = ChooseLogistics.objects.get(order_id=orderId)
                #cl = ChooseLogistics.objects.filter(order_id=orderId,available=1)
                if cl.channel_type == 'yanwen':
                    declare = getOrderDeclare(orderId)
                    if len(declare)>0:
                        declareInfo = dict()
                        od = OrderDeal.objects.get(orderId=orderId)
                        declareInfo['Quantity'] = str(od.productCount)
                        declareInfo['Channel'] = cl.channel
                        declareInfo['orderId'] = orderId
                        declareInfo['Memo'] = getDeliveryMemo(orderId)
                        declareInfo['GoodsName'] = declare
                        data['json'] = declareInfo
                        data['storeId'] = od.storeId
                        data['type'] = 'yanwen'
                        data['logicstics_no'] = ''

                        api = SMTAPI()
                        c = api.createoffline(store_id=data['storeId'],jsonData=json.dumps(data['json']))
                        #cl.is_submit=1
                        d = json.loads(c)
                        print(d)
                        if d.has_key('CallSuccess') and d['CallSuccess'] == 'true':
                            cl.logicstics_no = d['Response']['Epcode']
                            cl.is_success = 1
                            cl.save()
                            od.gmtApplyLogicsticsNo = datetime.datetime.now()
                            if od.deliveryLogisticsNo:
                                tmp = od.deliveryLogisticsNo
                            else:
                                tmp = ''
                            if d['Response']['Epcode'] not in tmp:
                                tmp += ',%s'%str(d['Response']['Epcode'])
                            tmp = tmp.replace(',,',',')
                            od.deliveryLogisticsNo = tmp.strip(',')
                            od.dealStatus = 4
                            od.save()
                            data['logicstics_no'] = d['Response']['Epcode']
                            data['success'] = True
                        else:
                            cl.isError = 1
                            cl.errorMsg = d['Response']['ReasonMessage']
                            cl.save()
                            od.dealStatus = 3
                            od.save()
                            data['success'] = False
                            data['msg'] = d['Response']['ReasonMessage'].decode()


                    else:
                        data['success'] = False
                        data['msg'] = u'缺少报关信息'.decode()
                elif cl.channel_type == 'aliexpress':
                    declare = getSmtDeclare(orderId)
                    if len(declare)>0:
                        declareInfo = dict()
                        od = OrderDeal.objects.get(orderId=orderId)
                        declareInfo["tradeOrderId"] =  int(orderId)
                        declareInfo["tradeOrderFrom"] =  "Sourcing"
                        declareInfo['Memo'] = getDeliveryMemo(orderId)
                        declareInfo["warehouseCarrierService"] =  cl.channel_code
                        declareInfo["declareProductDTOs"] = declare
                        data['json'] = declareInfo
                        data['storeId'] = od.storeId
                        data['type'] = 'aliexpress'

                        api = SMTAPI()
                        c = api.createWarehouseOrder(store_id=data['storeId'],jsonData=json.dumps(data['json']))
                        d = json.loads(c)
                        print(d)
                        if d.has_key('success') and d['success']:
                            cl.is_success = 1
                            od.gmtApplyLogicsticsNo = datetime.datetime.now()
                            time.sleep(5)
                            c = api.getOnlineLogisticsInfo(store_id=data['storeId'],orderId=orderId)
                            d = json.loads(c)
                            print(d)
                            if d.has_key('success') and d['success']:
                                if od.deliveryLogisticsNo:
                                    tmp = od.deliveryLogisticsNo
                                else:
                                    tmp = ''
                                for item in  d['result']:
                                    if item['lpNumber'] not in tmp:
                                        tmp += ',%s'%(item['lpNumber'])
                                    if item['internationallogisticsId'] not in tmp:
                                        tmp += ',%s'%(item['internationallogisticsId'])
                                tmp = tmp.replace(',,',',')
                                od.deliveryLogisticsNo = tmp.strip(',')
                                cl.lpNumber = d['result'][0]['lpNumber']
                                if d['result'][0]['internationallogisticsId'] != '':
                                    od.dealStatus = 4
                                    cl.logicstics_no = d['result'][0]['internationallogisticsId']
                                else:
                                    od.dealStatus = 9

                                data['lpNumber'] = d['result'][0]['lpNumber']
                                data['logicstics_no'] = d['result'][0]['internationallogisticsId']


                            od.save()
                        else:
                            cl.isError = 1
                            od.dealStatus = 3
                            od.save()

                        cl.save()

                        data['success'] = True
                    else:
                        data['success'] = False
                        data['msg'] = u'缺少报关信息'.decode()
                else:
                    data['success'] = False
                    data['msg'] = u'未知发货渠道'.decode()

            except Exception as e:
                print(e)
                data['success'] = False
                data['msg'] = u'没有选中物流信息'.decode()

        else:
            data['success'] = False
            data['msg'] = u'参数或内容不完整'.decode()

        '''
            if cl.count()>0:
                if cl[0].channel_type and cl[0].channel_type == 'yanwen':
                    declare = getOrderDeclare(orderId)
                    if len(declare)>0:
                        declareInfo = dict()
                        od = OrderDeal.objects.get(orderId=orderId)
                        declareInfo['Quantity'] = str(od.productCount)
                        declareInfo['Channel'] = cl[0].channel
                        declareInfo['orderId'] = orderId
                        declareInfo['Memo'] = getDeliveryMemo(orderId)
                        declareInfo['GoodsName'] = declare
                        data['json'] = declareInfo
                        data['storeId'] = od.storeId
                        data['type'] = 'yanwen'


                        #params = {'json':json.dumps(data['json']),'store_id':data['storeId']}
                        #requests.get('http://127.0.0.1:8900/erp/api/testSend/',params=params)

                        api = SMTAPI()
                        c = api.createoffline(store_id=data['storeId'],jsonData=json.dumps(data['json']))
                        d = json.loads(c)

                        print(d)

                        cl[0].is_submit=1
                        cl[0].save()

                        data['success'] = True

                    else:
                        data['success'] = False
                        data['msg'] = u'缺少报关信息'.decode()
                elif cl[0].channel_type and cl[0].channel_type == 'aliexpress':
                    declare = getSmtDeclare(orderId)
                    if len(declare)>0:
                        declareInfo = dict()
                        od = OrderDeal.objects.get(orderId=orderId)
                        declareInfo["tradeOrderId"] =  int(orderId)
                        declareInfo["tradeOrderFrom"] =  "Sourcing"
                        declareInfo['Memo'] = getDeliveryMemo(orderId)
                        declareInfo["warehouseCarrierService"] =  cl[0].channel_code
                        declareInfo["declareProductDTOs"] = declare
                        data['json'] = declareInfo
                        data['storeId'] = od.storeId
                        data['type'] = 'aliexpress'

                        #params = {'json':json.dumps(data['json']),'store_id':data['storeId']}
                        #requests.get('http://127.0.0.1:8900/erp/api/testSend/',params=params)

                        api = SMTAPI()
                        c = api.createWarehouseOrder(store_id=data['storeId'],jsonData=json.dumps(data['json']))
                        d = json.loads(c)
                        print(d)

                        cl[0].is_submit=1
                        cl[0].save()

                        data['success'] = True
                    else:
                        data['success'] = False
                        data['msg'] = u'缺少报关信息'.decode()

                else:
                    data['success'] = False
                    data['msg'] = u'未知发货渠道'.decode()
            else:
                data['success'] = False
                data['msg'] = u'没有选中物流信息'.decode()
        else:
            data['success'] = False
            data['msg'] = u'参数或内容不完整'.decode()
        '''

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def getOrderMemo(request):
    data = dict()
    if not request.user.is_authenticated():
        data['error'] = True
        data['msg'] = u'无权限访问！'.decode()
    else:
        orderId = request.GET.get('orderId',None)
        type = request.GET.get('type',None)
        if orderId:
            data['error'] = False
            memo = OrderMemo.objects.filter(order_id=orderId)
            if type:
                try:
                    type = int(type)
                    memo = memo.filter(type=type)
                except:
                    pass
            data['result'] = []
            if memo.count()>0:
                for item in memo:
                    data['result'].append({
                        'time':item.add_time.strftime('%Y-%m-%d %H:%I:%S'),
                        'type':str(item.type),
                        'content':item.content
                    })
        else:
            data['error'] = True
            data['msg'] = u'请传入订单ID！'.decode()

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def getChildOrder(request):
    data = dict()
    if not request.user.is_authenticated():
        data['error'] = True
        data['msg'] = u'无权限访问！'.decode()
    else:
        orderId = request.GET.get('orderId',None)
        if orderId:
            sql = 'SELECT childOrderId,orderId,lotNum,productAttributes,productCount,productId,productName,productUnit,skuCode,initOrderAmt_amount,initOrderAmt_currencyCode,productPrie_amount,logisticsType FROM e_order_child WHERE orderId="%s" ORDER BY initOrderAmt_amount DESC '%orderId
            childOrder = my_custom_sql(sql)
            if childOrder:
                data['error'] = False
                data['childOrder'] = []
                data['Total'] = len(childOrder)
                for item in childOrder:
                    '''
                    productId = item['productId']
                    productName = item['productName']
                    skuCode = item['skuCode']
                    productUnit = item['productUnit']
                    lotNum = item['lotNum']
                    productCount = item['productCount']
                    unitPrice = item['productPrie_amount']
                    currencyCode = item['initOrderAmt_currencyCode']
                    totalAmount = item['initOrderAmt_amount']
                    logisticsType = item['logisticsType']
                    productAttributes = json.loads(item['productAttributes'])
                    '''



                    #thumbnail = getThumbnail(item['productId'])
                    #declare = getDeclare(item['productId'])

                    data['childOrder'].append({'orderId':item['orderId'],'childOrderId':item['childOrderId'],'productId':item['productId'],'thumbnail':getThumbnail(item['productId']),'productName':item['productName'],'skuCode':item['skuCode'],'productUnit':item['productUnit'],
                                               'lotNum':item['lotNum'],'productCount':item['productCount'],'unitPrice':item['productPrie_amount'],'currencyCode':item['initOrderAmt_currencyCode'],
                                               'totalAmount':item['initOrderAmt_amount'],'logisticsType':translateLogistics(item['logisticsType']),'productAttributes':json.loads(item['productAttributes']),
                                               "declare":getDeclare(item['productId'])})

            else:
                data['error'] = True
                data['msg'] = u'无订单数据！'.decode()
        else:
            data['error'] = True
            data['msg'] = u'订单号有误！'.decode()

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def getThumbnail(productId):
    img = ''
    sql = 'SELECT image_url from e_product_img WHERE productId="%s" limit 1'%productId
    temp = my_custom_sql(sql)
    if temp:
        img += temp[0]["image_url"]
    return img


def editAddress(request):
    data = dict()
    if not request.user.is_authenticated():
        data['error'] = True
        data['msg'] = u'无权限访问！'.decode()
    else:
        orderId = request.GET.get('orderId',None)
        if orderId:
            country = request.GET.get('country',None)
            province = request.GET.get('province',None)
            city = request.GET.get('city',None)
            zip = request.GET.get('zip',None)
            contactPerson = request.GET.get('contactPerson',None)
            address1 = request.GET.get('address1',None)
            address2 = request.GET.get('address2',None)
            #phone = request.GET.get('phone',None)
            mobile = request.GET.get('mobile',None)

            if country and province and city and zip and contactPerson and address1 :
                sql = 'UPDATE e_order_receiptaddress SET `country`="%s",`province`="%s",`city`="%s",`zip`="%s",contactPerson="%s",detailAddress="%s" WHERE orderId="%s"'%(country.replace('"','\\"'),province.replace('"','\\"'),city.replace('"','\\"'),zip,contactPerson.replace('"','\\"'),address1.replace('"','\\"'),orderId)
                my_update_sql(sql)

            if mobile :
                sql = 'UPDATE e_order_receiptaddress SET `mobileNo`="%s" WHERE orderId="%s"'%(mobile,orderId)
                my_update_sql(sql)
            if address2 :
                sql = 'UPDATE e_order_receiptaddress SET `address2`="%s" WHERE orderId="%s"'%(address2.replace('"','\\"'),orderId)
                my_update_sql(sql)

            try:
                #odm = OrderDealMark.objects.get(order_id=orderId)
                #odm.address_check=1
                #odm.save()
                od = OrderDeal.objects.get(orderId=orderId)
                od.addressCheck = 1
                od.save()
                data['error'] = False
                data['msg'] = u'更新成功！'.decode()
            except:
                data['error'] = True
                data['msg'] = u'无效订单ID或订单未同步！'.decode()

        else:
            data['error'] = True
            data['msg'] = u'非法请求！'.decode()

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def getAddress(request):
    data = dict()
    if not request.user.is_authenticated():
        data['error'] = True
        data['msg'] = u'无权限访问！'.decode()
    else:
        orderId = request.GET.get('orderId',None)
        if orderId:
            sql = 'SELECT `detailAddress`,`address2`,`city`,`contactPerson`,`country`,`province`,`zip`,`phoneCountry`,`phoneArea`,`phoneNumber`,`mobileNo` FROM e_order_receiptaddress WHERE orderId="%s"'%orderId
            address = my_custom_sql(sql)

            if address:
                data['error'] = False
                data['address'] = address[0]
                countryCode = address[0]["country"]
                try:
                    c = Country.objects.get(country_code=countryCode)
                    data['address']['country_name'] = c.country_name
                    data['address']['country_name_cn'] = c.country_name_cn
                except:
                    data['address']['country_name'] = ''
                    data['address']['country_name_cn'] = ''
            else:
                data['error'] = True
                data['msg'] = u'无匹配订单！'.decode()

        else:
            data['error'] = True
            data['msg'] = u'订单ID有误！'.decode()
    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def getBuyerInfo(request):
    data = dict()
    if not request.user.is_authenticated():
        data['error'] = True
        data['msg'] = u'无权限访问！'.decode()
    else:
        uid = request.GET.get('uid',None)
        if uid:
            sql = 'SELECT `country`,`firstName`,`lastName`,`loginId`,`Email` FROM e_order_buyerinfo WHERE loginId="%s"'%uid
            buyer = my_custom_sql(sql)
            data['error'] = False
            data['buyerInfo'] = dict()
            if buyer:
                data['buyerInfo'] = buyer[0]
        else:
            data['error'] = True
            data['msg'] = u'买家账号有误！'.decode()


    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def getOrderDetail(request):
    orderId = request.GET.get('id')
    if orderId:
        orderData = dict()

        sql = 'SELECT `SKUNAME`,`IMAGEURL`,`PROFIT`,`COUNTRY`,`CREATE_DATE`,`EXPRESS_COMPANY`,`STOREID` FROM `m_profit` limit 30'


    return HttpResponse(json.dumps(orderData),content_type='application/json; charset=utf-8')


def ajaxSearch(request):
    data = dict()

    q = request.GET.get('q','')

    sql = 'SELECT `productId` as id,`productId`,`subject`,substring_index(`imageURLs`,";",1) as imageURLs,`packageLength`,`packageWidth`,`packageHeight`,`grossWeight`,`productPrice`,`currencyCode`,`productStatusType` FROM `e_product`'

    if q != '':
        sql += 'WHERE `subject` like "%'+q.replace('"','\\"')+'%" '
        sql += ' OR `productId` like "%'+q.replace('"','\\"')+'%" '

    sql += ' LIMIT 30'

    pData = my_custom_sql(sql)
    data["total_count"] = len(pData)
    data["items"] = pData


    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def logisticsAPI(request):
    data = dict()
    if not request.user.is_authenticated():
        data['msg'] = u'未授权！'.decode()
        data['error'] = True
    else:
        api = LOGISTICS()
        apiName = request.GET.get('_api_',None)
        country = request.GET.get('country',None)
        weight = request.GET.get('weight',None)
        IS_ELECTRIC = request.GET.get('IS_ELECTRIC',None)
        lengths = request.GET.get('lengths',None)
        widths = request.GET.get('widths',None)
        heights = request.GET.get('heights',None)
        jinghuojia = request.GET.get('jinghuojia',None)
        profit = request.GET.get('profit',None)

        if apiName in api.nameList:
            data['_API_'] = apiName
            if apiName == 'calculate':
                if country and weight:

                    c = api.calculate(country,weight,IS_ELECTRIC,lengths,widths,heights,jinghuojia,profit)
                    d = json.loads(c)

                    data["data"] = d
                    data['error'] = False

                else:
                    data['msg'] = u'缺少必填参数｛国家｜重量｝！'.decode()
                    data['error'] = True



        else:
            data['msg'] = u'非法接口名称！'.decode()
            data['error'] = True

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def getStoreServiceIP(storeId):
    result = ''
    sql = 'SELECT SERVICE_IP FROM sys_store WHERE ID=%s'%storeId
    data = my_custom_sql(sql)
    if len(data)>0:
        result += data[0]['SERVICE_IP']
    return result

def transferPrice(aa):
    bb = aa.split('.')
    if len(bb) > 1:
        return float(bb[0]+'.'+bb[1])
    else:
        return float(aa)

def saveYWLogisticsChanel(request):
    data = dict()
    data['success'] = False
    styleList = ['A4L', 'A4LI', 'A4LC', 'A4LCI', 'A6L', 'A6LI', 'A6LC', 'A6LCI', 'A10x10L', 'A10x10LI', 'A10x10LC', 'A10x10LCI']
    channelId = request.GET.get('channelId',None)
    diyName = request.GET.get('diyName',None)
    maxDeclareAmount = request.GET.get('maxDeclareAmount',None)
    printStyle = request.GET.get('printStyle','None')
    if channelId:
        sql = 'UPDATE y_channel SET '
        n = 0
        if diyName != '':
            sql += 'diyName="%s" '%(diyName.replace('"','\\"'))
            n = 1
        if maxDeclareAmount != '':
            if n == 1:
                sql += ','
            sql += ' maxDeclareAmount=%s '%(str(transferPrice(maxDeclareAmount)))
            n = 1
        if printStyle != 'None' and printStyle in styleList:
            if n == 1:
                sql += ','
            sql += ' printStyle="%s" '%(printStyle)
        sql += 'WHERE YWCHANNEL_ID=%s'%channelId

        my_update_sql(sql)
        data['success'] = True

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def saveSmtLogisticsChanel(request):
    data = dict()
    data['success'] = False
    channelId = request.GET.get('channelId',None)
    diyName = request.GET.get('diyName',None)
    if channelId and diyName:
        sql = 'UPDATE e_smt_logistics_channel SET diyName="%s" WHERE logisticsServiceId="%s"'%(diyName.replace('"','\\"'),channelId)
        my_update_sql(sql)
        data['success'] = True

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def getYWLogisticsChanelInfo(request):
    data = dict()
    q = request.GET.get('q','')
    sql = 'SELECT * FROM `y_channel` WHERE YWCHANNEL_ID=%s'%q
    cData = my_custom_sql(sql)
    data['total_count'] = len(cData)
    if data['total_count']>0:
        data['result'] = cData[0]
    else:
        data['result'] = {}

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')

def ywLogisticsChannelEditForm(request):
    q = request.GET.get('q','')
    sql = 'SELECT * FROM `y_channel` WHERE YWCHANNEL_ID=%s'%q
    cData = my_custom_sql(sql)
    if len(cData)>0:
        result = cData[0]
    else:
        result = ''
    render(request, 'aside/erp/html/channelForm.html', locals())

def getSmtLogisticsChanelInfo(request):
    data = dict()
    q = request.GET.get('q','')
    sql = 'SELECT `logisticsServiceId`,`warehouseName`,`logisticsTimeliness`,`logisticsServiceName` FROM `e_smt_logistics_channel` WHERE logisticsServiceId="%s"'%q
    cData = my_custom_sql(sql)
    data['total_count'] = len(cData)
    if data['total_count']>0:
        data['result'] = cData[0]
    else:
        data['result'] = {}

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')

def getSmtLogisticsChanel(request):
    data = dict()
    q = request.GET.get('q','')
    sql = 'SELECT `logisticsServiceId`,`warehouseName`,`logisticsTimeliness`,`enabled` FROM `e_smt_logistics_channel` '
    if q != '':
        sql += ' WHERE smtServiceName="%s"'%q

    sql += ' ORDER BY enabled DESC'
    cData = my_custom_sql(sql)
    data["total_count"] = len(cData)
    data["result"] = cData

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')
    #return render(request, 'aside/erp/html/smtChannelService.html', locals())

def searchLogisticsChanel(request):
    data = dict()

    q = request.GET.get('q','')

    #sql = 'SELECT `LOGISTICSL_CHANNEL_ID` as id,`NAME`,`TYPE`,`SHIXIAO` FROM `e_logisticsl_channel`'
    sql = 'SELECT `YWCHANNEL_ID` as id,`NAME`,`YWCHANNEL_ID` FROM `y_channel`'

    if q != '':
        sql += 'WHERE `STATUS`="true" AND `NAME` like "%'+q.replace('"','\\"')+'%" '
        #sql += ' OR `productId` like "%'+q.replace('"','\\"')+'%" '

    #sql += ' LIMIT 30'

    cData = my_custom_sql(sql)
    data["total_count"] = len(cData)
    data["items"] = cData


    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')

def smtAPI(request):
    data = dict()
    if not request.user.is_authenticated():
        data['msg'] = u'未授权！'.decode()
        data['error'] = True
    else:
        api = SMTAPI()
        apiName = request.GET.get('_api_',None)
        store_id = request.GET.get('store_id',None)
        #user_id = request.GET.get('user_id',None)
        productId = request.GET.get('productId',None)
        skuId = request.GET.get('skuId',None)
        stock = request.GET.get('stock',None)
        locale = request.GET.get('locale',None)
        subject = request.GET.get('subject',None)
        detail = request.GET.get('detail',None)
        name = request.GET.get('name',None)
        parentId = request.GET.get('parentId',None)
        categoryId = request.GET.get('categoryId',None)
        sizechartId = request.GET.get('sizechartId',None)
        productCategoryAttributes = request.GET.get('productCategoryAttributes',None)
        groupIds = request.GET.get('groupIds',None)
        productSkus = request.GET.get('productSkus',None)
        productProperties = request.GET.get('productProperties',None)
        fiedName = request.GET.get('fiedName',None)
        fiedvalue = request.GET.get('fiedvalue',None)
        productIds = request.GET.get('productIds',None)
        url = request.GET.get('url',None)
        productIdList = request.GET.get('productIdList',None)
        templateId = request.GET.get('templateId',None)
        moduleStatus = request.GET.get('moduleStatus',None)
        mType = request.GET.get('mType',None)
        page = request.GET.get('page',None)
        moduleId = request.GET.get('moduleId',None)

        jsonData = request.GET.get('jsonData',None)
        orderId = request.GET.get('orderId',None)

        serviceName = request.GET.get('serviceName',None)
        logisticsNo = request.GET.get('logisticsNo',None)
        sendType = request.GET.get('sendType',None)
        trackingWebsite = request.GET.get('trackingWebsite',None)
        eType = request.GET.get('eType',None)
        Epcode = request.GET.get('Epcode',None)

        internationalLogisticsId = request.GET.get('internationalLogisticsId',None)

        weight = request.GET.get('weight',None)
        length = request.GET.get('length',None)
        width = request.GET.get('width',None)
        height = request.GET.get('height',None)
        internationalLogisticsId = request.GET.get('internationalLogisticsId',None)

        if store_id == None or store_id == '':
            data['msg'] = u'无效的店铺ID！'.decode()
            data['error'] = True
        else:
            serviceIP = getStoreServiceIP(store_id)
            if serviceIP == '':
                data['msg'] = u'服务器信息错误！'.decode()
                data['error'] = True
            else:
                api.api_root = serviceIP

                if apiName in api.nameList:
                    data['_API_'] = apiName

                    if apiName == 'findAeProductStatusById':
                        if store_id and productId:
                            c = api.findAeProductStatusById(store_id,productId)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    elif apiName == 'renewExpire':
                        if store_id and productId:
                            c = api.renewExpire(store_id,productId)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True
                    elif apiName == 'getRemainingWindows':
                        if store_id:
                            c = api.getRemainingWindows(store_id)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True
                    elif apiName == 'editSingleSkuStock':
                        if store_id and productId and skuId and stock:
                            c = api.editSingleSkuStock(store_id,productId,skuId,stock)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:

                                sql = 'UPDATE `e_product_sku` SET `ipmSkuStock`="%s" WHERE `productId`="%s" and skuId="%s"'%(stock,productId,skuId)
                                conn = linkDB()
                                cusour = conn.cursor()
                                cusour.execute(sql)
                                conn.commit()

                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True
                    elif apiName == 'editMultilanguageProduct':
                        if store_id and productId and locale and subject and detail:
                            c = api.editMultilanguageProduct(store_id,productId,locale,subject,detail)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True
                    elif apiName == 'createProductGroup':
                        if store_id and name and parentId:
                            c = api.createProductGroup(store_id,name,parentId)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True
                    elif apiName == 'getSizeChartInfoByCategoryId':
                        if store_id and categoryId:
                            c = api.getSizeChartInfoByCategoryId(store_id,categoryId)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True
                    elif apiName == 'setSizeChart':
                        if store_id and productId and sizechartId:
                            c = api.setSizeChart(store_id,productId,sizechartId)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    elif apiName == 'getWindowProducts':
                        if store_id:
                            c = api.getWindowProducts(store_id)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True
                    elif apiName == 'editProductCategoryAttributes':
                        if store_id and productId and productCategoryAttributes:
                            c = api.editProductCategoryAttributes(store_id,productId,productCategoryAttributes)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True
                    elif apiName == 'setGroups':
                        if store_id and productId and groupIds:
                            c = api.setGroups(store_id,productId,groupIds)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    elif apiName == 'queryProductGroupIdByProductId':
                        if store_id and productId:
                            c = api.queryProductGroupIdByProductId(store_id,productId)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    elif apiName == 'getProductGroupList':
                        if store_id:
                            c = api.getProductGroupList(store_id)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True
                    elif apiName == 'editProductCidAttIdSku':
                        if store_id and productId and categoryId and productSkus and productProperties:
                            c = api.editProductCidAttIdSku(store_id,productId,categoryId,productSkus,productProperties)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    elif apiName == 'editSimpleProductFiled':
                        if store_id and productId and fiedName and fiedvalue:
                            c = api.editSimpleProductFiled(store_id,productId,fiedName,fiedvalue)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:

                                if fiedName == 'subject':
                                    fiedvalue = fiedvalue.replace('"','\\"')
                                    sql = 'UPDATE `e_product` SET `subject`="%s" WHERE `productId`="%s"'%(fiedvalue,productId)
                                    conn = linkDB()
                                    cusour = conn.cursor()
                                    cusour.execute(sql)
                                    conn.commit()

                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True
                    elif apiName == 'getAtributeMissingProductList':
                        if store_id and productId and productIds:
                            c = api.getAtributeMissingProductList(store_id,productIds)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    elif apiName == 'claimTaobaoProducts4API':
                        if store_id and url:
                            c = api.claimTaobaoProducts4API(store_id,url)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    elif apiName == 'setShopwindowProduct':
                        if store_id and productIdList:
                            c = api.setShopwindowProduct(store_id,productIdList)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    elif apiName == 'queryPromiseTemplateById':
                        if store_id and templateId:
                            c = api.queryPromiseTemplateById(store_id,templateId)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    elif apiName == 'listTbProductByIds':
                        if store_id and productIds:
                            c = api.listTbProductByIds(store_id,productIds)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    elif apiName == 'listTbProductByIds':
                        if store_id and productIds:
                            c = api.listTbProductByIds(store_id,productIds)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    elif apiName == 'findAeProductDetailModuleListByQurey':
                        if store_id and moduleStatus and mType and page:
                            c = api.findAeProductDetailModuleListByQurey(store_id,moduleStatus,mType,page)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    elif apiName == 'findAeProductModuleById':
                        if store_id and moduleId:
                            c = api.findAeProductModuleById(store_id,moduleId)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    elif apiName == 'onlineAeProduct':
                        if store_id and productIds:
                            c = api.onlineAeProduct(store_id,productIds)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    elif apiName == 'offlineAeProduct':
                        if store_id and productIds:
                            c = api.offlineAeProduct(store_id,productIds)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    #创建线下发货
                    elif apiName == 'createoffline':
                        if store_id and jsonData:
                            c = api.createoffline(store_id,jsonData)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    #查询线下发货订单
                    elif apiName == 'findOfflineEpcode':
                        if orderId:
                            c = api.findOfflineEpcode(orderId)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    #获取线下发货pdf面单
                    elif apiName == 'getOfflinePDFStream':
                        if Epcode and eType:
                            c = api.getOfflinePDFStream(Epcode,eType)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                if d['file'] == '':
                                    data['error'] = True
                                    data['msg'] = u'获取不到文件！'.decode()
                                else:
                                    data["path"] = api.api_root
                                    data["file"] = d['file']
                                    data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    #填写发货通知
                    elif apiName == 'sellerShipment':
                        if store_id and serviceName and logisticsNo and sendType and orderId:
                            if trackingWebsite != '':
                                c = api.sellerShipment(store_id,serviceName,logisticsNo,sendType,orderId,trackingWebsite)
                            else:
                                c = api.sellerShipment(store_id=store_id,serviceName=serviceName,logisticsNo=logisticsNo,sendType=sendType,orderId=orderId)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True
                    #根据交易订单获取线上发货物流服务列表
                    elif apiName == 'getOnlineLogisticsServiceListByOrderId':
                        if store_id and serviceName and logisticsNo and sendType and orderId and trackingWebsite:
                            c = api.getOnlineLogisticsServiceListByOrderId(store_id,orderId,weight,length,width,height)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True


                    #创建线上发货物流订单信息
                    elif apiName == 'createWarehouseOrder':
                        if store_id and jsonData:
                            c = api.createWarehouseOrder(store_id,jsonData)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    #获取线上发货物流订单信息
                    elif apiName == 'getOnlineLogisticsInfo':
                        if store_id and orderId:
                            c = api.getOnlineLogisticsInfo(store_id,orderId)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                data["data"] = d
                                data['error'] = False
                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True

                    #获取线上发货标签
                    elif apiName == 'getPrintInfo':
                        if store_id and internationalLogisticsId:
                            c = api.getPrintInfo(store_id,internationalLogisticsId)
                            d = json.loads(c)
                            if d.has_key('api_status'):
                                data['error'] = True
                                data['msg'] = u'接口异常！'.decode()
                            else:
                                if d['file'] == '':
                                    data['error'] = True
                                    data['msg'] = u'获取不到文件！'.decode()
                                else:
                                    data["path"] = api.api_root
                                    data["file"] = d['file']
                                    data['error'] = False

                        else:
                            data['msg'] = u'缺少参数！'.decode()
                            data['error'] = True


                else:
                    data['msg'] = u'非法接口名称！'.decode()
                    data['error'] = True

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def random_str(leng):
    str = ''
    chars = 'AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz0123456789'
    length = len(chars) - 1
    for i in range(leng):
        str+=chars[random.randint(0, length)]

    l = list(str)
    l.sort()
    str = "".join(reversed(l))
    return str.upper()


def random_digital(leng):
    str = ''
    chars = '012345678901234567890123456789'
    length = len(chars) - 1
    for i in range(leng):
        str+=chars[random.randint(0, length)]

    return str

def getSkuCode(request):
    data = dict()
    data["skuCode"] = random_digital(4)+'-'+random_str(4)
    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def md5Str(str):
    w = hashlib.md5()
    w.update(str)
    return w.hexdigest()


def TranslateAPI(request):
    words = request.GET.get('words',None)
    to = request.GET.get('to',None)
    data = dict()
    if words:
        md5 = md5Str(words)
        length = len(words)

        item = TranslateContent.objects.filter(md5=md5)
        if item.count()>0:
            data["success"] = 1
            data["result"] = item[0].dst
            data["from"] = 'DATABASE'
        else:
            translator = TRANSLATOR()
            if to:
                result = translator.baiduTranslate(word=words,to=to)
            else:
                result = translator.baiduTranslate(words)
            #print(result)
            if result.has_key('result'):
                data["success"] = 1
                data["result"] = result["result"]
                data["from"] = 'BAIDU'
                try:
                    TranslateContent.objects.create(md5=md5,src=words.replace('"','\\"'),dst=data["result"].replace('"','\\"'))
                except:
                    pass
            else:
                data["success"] = 0
                data["msg"] = result["msg"]

    else:
        data["msg"] = u'请传入翻译的句子'.decode()
        data["success"] = 0


    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')